Oracle数据库对表进行表空间移动(move tablespace)

在oracle中有时需要把表移动到其他表空间中，下面是移动的步骤。
首先,使用下面的命令移动:
alter table table_name move tablespace tablespace_name;
然后,如果有索引的话必须重建索引:
alter index index_name rebuild tablespace tablespace_name;

需要注意的地方是：
1、若表中需要同时移动lob相关字段的数据，就必需用如下的含有特殊参数据的语句来完成。

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

如果表特别多，可以生产一个执行的脚本。
select 'alter index '||OWNER||'.'||index_NAME||' rebuild tablespace tablespace_name;' from dba_indexes where OWNER='USERS';
select 'alter table '||OWNER||'.'||TABLE_NAME||' move tablespace tablespace_name;' from dba_tables where OWNER='USERS';

 

Oracle table move tablespace操作总结:
1.非分区表move
ALTER TABLE IDW_FINA.OS_OA_FAMILY_ORDERITEM move tablespace IDWD_TBS002;

 

2.分区表move
分区表move的话，要按照partition逐个进行move，如果有subpartition的话要subpartition逐个进行move

alter table BDHI.FCT_ROAMING_MSG move partition FCT_ROAMING_MSG20160210 tablespace IDWD_TBS002

alter table BDHI.TEST_MOVE_PARTITION_0617 move subpartition P2011_D4 tablespace IDWD_TBS002

move完成后修改partition的default tablespace attributes

ALTER TABLE TEST_MOVE_PARTITION_0617 MODIFY DEFAULT ATTRIBUTES FOR PARTITION P2011 TABLESPACE IDWD_TBS002;

3.索引move的话，需要rebuild索引
alter index IDW_WF.IDX_DA_COMPLETE rebuild tablespace IDWD_TBS002

4.如果有lob字段的话 lob字段需要单独move
alter table BDHI.TEST move lob(A) store as (tablespace IDWD_TBS002)

Oracle 12.1开始，可以对分区和子分区进行online move：

ALTER TABLE t1 MOVE PARTITION part_2015 TABLESPACE users ONLINE UPDATE INDEXES;

ALTER TABLE t1 MOVE SUBPARTITION SYS_SUBP793 TABLESPACE users ONLINE UPDATE INDEXES;

Oracle 12.2以后，可以对常规表进行Online move，会自动重建索引：

ALTER TABLE t1 MOVE TABLESPACE users ONLINE;

There are some restrictions associated with online moves of tables described here.

It can't be combined with any other clause.
It can't be used on a partitioned index-organized table or index-organized tables that have a column defined as a LOB, VARRAY, Oracle-supplied type, or user-defined object type.
It can't be used if there is a domain index on the table.
Parallel DML and direct path inserts are not supported against an object with an ongoing online move.

关于Online 和 UPDATE INDEXES 的作用：
Moving a table changes the rowids of the rows in the table.
If you move a table and include the ONLINE keyword and the UPDATE INDEXES clause, then the indexes remain usable during the move operation.
If you include the UPDATE INDEXES clause but not the ONLINE keyword, then the indexes are usable immediately after the move operation.
The UPDATE INDEXES clause can only change the storage properties for the global indexes on the table or storage properties for the index partitions of any global partitioned index on the table.
If you do not include the UPDATE INDEXES clause, then the changes to the rowids cause the indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes receive an ORA-01502 error.
In this case, the indexes on the table must be dropped or rebuilt.
————————————————
版权声明：本文为CSDN博主「jzzw」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u014710633/article/details/106825424