怎么理解ORACLE MOVE 表空间

怎么理解ORACLE MOVE 表空间，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

move一个表到另外一个表空间时,索引不会跟着一起move，而且会失效（LOB类型例外）。做表空间转移之前，被转移表的用户必须在目的表空间上有使用表空间的权限，否则会报错：ORA-01950: no privileges on tablespace...

表move分为

1.普通表move

2.分区表move

3.LONG

4.LOB大字段类型move

5．索引的move通过rebuild来实现

一、move普通表、索引

1、基本语法：

a、alter table table_name tmove

tablespace xxx;

b、alter index index_name rebuild

tablespace xxx;

move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败

，其他如外键，非空约束，缺省值等不会失效。

2、重新创建主键或索引基本语法为：

a、alter index index_name rebuild;

b、alter index pk_name rebuild;

3、move索引用rebuild语法：

a、alter index index_name rebuild tablespace tbs_name;

b、alter index pk_name rebuild tablespace tbs_name;

二、move分区表及索引

和普通表一样，分区表索引会失效，区别的仅仅是语法而已。

1、分区基本语法

注：如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。

如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);

如：

ALTER TABLE PART_ALARM move SUBPARTITION p_01 TABLESPACE usersPARALLEL (DEGREE 2);

--全局索引

ALTER INDEX GX1_ PART_ALARM REBUILD tablespace usersPARALLEL (DEGREE 2);

--分区索引

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_01 TABLESPACE users1PARALLEL (DEGREE 2);

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_02 TABLESPACE users2PARALLEL (DEGREE 2);

………………

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_0n TABLESPACE usersnPARALLEL (DEGREE 2);

2、移动表的某个分区

ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;

3、重建全局索引

ALTER INDEX global_index REBUILD;

或

ALTER INDEX global_index REBUILD tablespace tbs_name;

4、重建局部索引

ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;

或

ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;

提示：

USER_PART_TABLES

USER_IND_PARTITIONS

USER_IND_SUBPARTITIONS

USER_LOB_PARTITIONS

USER_LOB_SUBPARTITIONS

USER_PART_INDEXES

USER_PART_LOBS

user_segments

三、move LONG类型

可以使用DBMS_REDEFINITION包可以提供一些方便，不过没用过。

long类型不能通过MOVE来传输。特别提示，尽量不要用LONG类型，特难管理。

1、LONG不能使用insert into ... select ...等带select的模式。

如

create table t123 (id int,en long);

则

insert into t123(id,en) select * from t123;

报告错误，可以用pl/sql来帮助解决，如：

declare

cursor cur_t123 is select * from t123;

use_t123 cur_t123%rowtype;

begin

open cur_t123;

loop

fetch cur_t123 into use_t123;

exit when cur_t123%notfound;

insert into t123(id,en) values (use_t123.id,use_t123.en);

end loop;

close cur_t123;

end;

/

2、LONG类型字段的表的转移

1.)create新表的方法。

a.create一个新的表，存储在需要转移的表空间。

b.创建新的索引（使用tablespace子句指定新的表空间）。

c.把数据转移过来

2.)用COPY的方法

copy from bigboar/bigboar@bigboar_sid insert t123(id,en) using select id,en from t123;

3、直接就把LONG转换成CLOB类型

create table t321(id int,en clob) tablespace users;

insert into t321(id,en) select id,to_lob(en) from t123;

4、exp/imp

exp bigboar/bigboar file=a.dat tables=t123

imp bigboar/bigboar file=a.dat full=y IGNORE =y

a.drop掉旧表。

b.rename新表为旧表表名。

四、LOB类型

在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type=LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时，LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：

alter table t321 move tablespace users;

alter table t321 move lob(en) store as (tablespace users);

大家都知道在我们建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据，另一个用来存放索引，并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tb_name move tablespace tbs_name;来对表做空间迁移时只能移动非lob字段以外的数据，而如果我们要同时移动lob相关字段的数据，我们就必需用如下的含有特殊参数据的文句来完成：

alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablesapce tbs_name);

五、实际工作操作示例

1、说明

把SOURCE_TABLESPACE表空间上的表移动到DEST_TABLESPACE表空间上，删除SOURCE_TABLESPACE，释放磁盘空间，重新再建一个比原先小的SOURCE_TABLESPACE表空间，再把表移回SOURCE_TABLESPACE。

2、步骤

1.)检查表空间表字段有没有分区、包含LOB字段和LONG字段等

2.) 检查表空间有什么内容（包括表、索引、分区等）

select segment_name,segment_type from dba_segments where tablespace_name=SOURCE_TABLESPACE;

3.) 移动表和索引

alter table IBSS.TB_CM_MSPARAM_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_SPRESENT_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSITEM_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_SERVACCT_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_BANKACCT_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_ACCT_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSINFO_HIST move tablespace DEST_TABLESPACE;

alter table IBSS.TB_CM_MSITEMR_HIST move tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSINFOUST rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSINMSINF rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSIN _DISC rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSITEMNG rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_M_MSINFO rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSOBJET rebuild tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSPRINFO rebuild tablespace DEST_TABLESPACE;

4.) 删除表空间SOURCE_TABLESPACE

DROP TABLESPACE HPMDBS1 INCLUDING CONTENTS and DATAFILES;

5.) 重新创建表空间

CREATE TABLESPACE HPMDBS1 DATAFILE

/opt/oracle/oradata1/tbs0101.dbf SIZE 6192M AUTOEXTEND OFF;

6.) 重新移动表和索引到原空间

alter table IBSS.TB_CM_MSPARAM_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_SPRESENT_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSITEM_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_SERVACCT_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_BANKACCT_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_ACCT_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSINFO_HIST move tablespace SOURCE_TABLESPACE;

alter table IBSS.TB_CM_MSITEMR_HIST move tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSINFOUST rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSINMSINF rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSIN _DISC rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSITEMNG rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_M_MSINFO rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSOBJET rebuild tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSPRINFO rebuild tablespace SOURCE_TABLESPACE;

7.) 检查原先表和索引是否正确，检查表空间索引有没有无效。

SELECT index_name,index_type, STATUS,partitioned, table_name

FROM User_Indexes

Where status=’UNUSABLE’;

六、常用SQL

     该方法可以用来释放被表空间占用的大量的磁盘空间，如果数据对象非常多就很麻烦。可以用以下的SQL来批量处理。

1、重建分区/普通表和索引的拼接sql语句

SELECT ALTER INDEX ||t1.owner||.||segment_name|| REBUILD ||||

CASE WHEN t1.segment_type = INDEX PARTITION THEN PARTITION ||partition_name

WHEN t1.segment_type = INDEX SUBPARTITION THEN SUBPARTITION ||partition_name

      ELSE

      END

      || tablespace ||&DEST_TABLESPACE||;

FROM dba_segments t1

WHERE tablespace_name=&SOURCE_TABLESPACE AND segment_type IN (INDEX,INDEX PARTITION,INDEX SUBPARTITION)

UNION

SELECT ALTER TABLE ||t1.owner||.||segment_name|| MOVE ||||

CASE WHEN t1.segment_type = TABLE PARTITION THEN PARTITION ||partition_name

WHEN t1.segment_type = TABLE SUBPARTITION THEN SUBPARTITION ||partition_name

      ELSE

      END

|| tablespace ||&DEST_TABLESPACE||;

FROM dba_segments t1

WHERE tablespace_name=&SOURCE_TABLESPACE AND segment_type IN (TABLE,TABLE PARTITION)

ORDER BY 1 DESC;

2、转移LOB字段SQL

SELECT ALTER TABLE ||t2.owner||.||t2.table_name|| move lob (||t1.segment_name||)|| STORE AS (|| tablespace ||&end_tablespace||);

FROM dba_segments t1,dba_lobs t2

WHERE t1.segment_name=t2.segment_name AND tablespace_name=&source_tablespace AND segment_type IN (LOBINDEX,LOBSEGMENT,LOB PARTITION);

看完上述内容，你们掌握怎么理解ORACLE MOVE 表空间的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！