本文共 2943 字，大约阅读时间需要 9 分钟。
现在需要将一些分区表移到别的表空间下去，且该表是arc表，目前是没有任何程序访问，不需使用在线重定义，可以直接move.
SQL> alter table JUSTIN move tablespace ARC;alter table JUSTIN move tablespace ARC *ERROR at line 1:ORA-14511: cannot perform. operation on a partitioned object分区表必须以分区为单位进行move，可以使用以下sql
Elapsed: 00:00:00.00
SQL> select 'alter table '||table_name||' move partition '||partition_name||' tablespace ARC;' from user_tab_partitions where table_name ='JUSTIN';'ALTERTABLE'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEARC
--------------------------------------------------------------------------------alter table JUSTIN move partition P0912 tablespace ARC;alter table JUSTIN move partition P1003 tablespace ARC;alter table JUSTIN move partition P1006 tablespace ARC;alter table JUSTIN move partition P1009 tablespace ARC;alter table JUSTIN move partition P1012 tablespace ARC;alter table JUSTIN move partition P1103 tablespace ARC;alter table JUSTIN move partition P1106 tablespace ARC;alter table JUSTIN move partition P1109 tablespace ARC;alter table JUSTIN move partition P1112 tablespace ARC;alter table JUSTIN move partition P1203 tablespace ARC;alter table JUSTIN move partition P1206 tablespace ARC;11 rows selected.
Elapsed: 00:00:00.07
SQL> alter table JUSTIN move partition P0912 tablespace ARC;alter table JUSTIN move partition P1003 tablespace ARC;alter table JUSTIN move partition P1006 tablespace ARC;alter table JUSTIN move partition P1009 tablespace ARC;alter table JUSTIN move partition P1012 tablespace ARC;alter table JUSTIN move partition P1103 tablespace ARC;alter table JUSTIN move partition P1106 tablespace ARC;alter table JUSTIN move partition P1109 tablespace ARC;alter table JUSTIN move partition P1112 tablespace ARC;alter table JUSTIN move partition P1203 tablespace ARC;alter table JUSTIN move partition P1206 tablespace ARC;Table altered.
Elapsed: 00:00:16.12
SQL> Table altered.Elapsed: 00:00:06.99
SQL> Table altered.Elapsed: 00:00:13.04
SQL> Table altered.Elapsed: 00:00:20.05
SQL> Table altered.Elapsed: 00:00:07.36
SQL> Table altered.Elapsed: 00:00:00.07
SQL> Table altered.Elapsed: 00:00:00.03
SQL> Table altered.Elapsed: 00:00:00.04
SQL> Table altered.Elapsed: 00:00:00.04
SQL> Table altered.Elapsed: 00:00:00.04
SQL> Table altered.Elapsed: 00:00:00.03
接下来需要重建索引，分区表上的索引分为分区索引和全局索引，需要区别开来进行重建重建分区索引SQL> select 'alter index '||index_name||' rebuild partition '||partition_name||' online tablespace ARC;' from user_ind_partitions where index_name in ( 2 select index_name from user_indexes where table_name ='JUSTIN') status != 'USABLE';no rows selected
重建全局索引SQL> select 'alter index '||index_name||' rebuild online tablespace ARC;' from user_indexes where table_name ='JUSTIN' 2 and status != 'USABLE';no rows selected
不过这个arch表没有索引，那就不需要重建了，move完成。来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-709372/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-709372/