现在需要将一些分区表移到别的表空间下去,且该表是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/