move分区表

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