Oracle table move tablespace

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.