ORA-14511 파티셔닝 테이블 MOVE

How to move partitioned tables to different tablespace Yesterday I was trying to move a partitioned table to different tablespace using move command I got the following error:-

SQL> alter table partition move tablespace users parallel 10;
alter table partition move tablespace users parallel 10
            *
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

Default tablespace of partitioned table :-

SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION';

TABLE_NAME PARTITIONING_TYPE DEF_TABLESPACE_NAME

PARTITION LIST SYSTEM

Changing the default tablespace of Partition table. Now new partitions will be created in the new default tablespace, but old partitions will remain in the old default tablespace :-

SQL> alter table partition modify default attributes tablespace users;

Table altered.

SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION';

TABLE_NAME PARTITIONING_TYPE DEF_TABLESPACE_NAME

PARTITION LIST USERS

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 SYSTEM PARTITION PAR2 SYSTEM

SQL> SELECT * FROM PARTITION;

        ID NAME
---------- ---------------------
         1 d
         3 f
         7 y
         8 t

Analyzing above select statements, table partition have 4 records but records won't reflect in the NUM_ROWS column of USER_TAB_PARTITIONS  view. We need to gather the stats of Table "PARTITION" to reflect the records in NUM_ROWS column.

<SOURCE LANG=SQL>
SQL> SHOW USER
USER is "SYS"
SQL> EXEC DBMS_STATS.gather_table_stats('SYS', 'PARTITION', granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION'

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 SYSTEM 2 PARTITION PAR2 SYSTEM 2

Moving OLD partitions to different tablespace :-

SQL> select 'alter table ' || table_name || ' move partition ' || partition_name|| ' tablespace users parallel 10;' "PARTITION_MOVE_SCRIPT" from user_tab_partitions where table_name='PARTITION';

PARTITION_MOVE_SCRIPT

alter table PARTITION move partition PAR1 tablespace users parallel 10; alter table PARTITION move partition PAR2 tablespace users parallel 10;

After moving a table or partitioned table to different tablespace , indexes associated to the tablespace become unusable. We need to rebuild the associated indexes to make them usable.

Status of Indexes before moving a table :-

SQL>  select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM';

INDEX_NAME PARTITION_NAME STATUS

PAR_IDX PAR1 USABLE PAR_IDX PAR2 USABLE

SQL> alter table PARTITION move partition PAR1 tablespace users parallel 10;

Table altered.

SQL> alter table PARTITION move partition PAR2 tablespace users parallel 10;

Table altered.

SQL>
SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 USERS 2 PARTITION PAR2 USERS 2

SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM';

INDEX_NAME PARTITION_NAME STATUS

PAR_IDX PAR1 UNUSABLE PAR_IDX PAR2 UNUSABLE

It seems that the indexes becomes unusable. We need to rebuild the indexes to make them usable.

SQL> select 'alter index ' || a.index_name ||  ' rebuild partition ' || a.PARTITION_NAME || ' tablespace USERS parallel 10;' from user_ind_partitions a, user_tab_partitions b where a.partition_name=b.partition_name and b.table_name='PARTITION';

'ALTERINDEX'||A.INDEX_NAME||'REBUILDPARTITION'||A.PARTITION_NAME||'TABLESPACEUSERSPARALLEL10;'

alter index PAR_IDX rebuild partition PAR1 tablespace USERS parallel 10; alter index PAR_IDX rebuild partition PAR2 tablespace USERS parallel 10;

SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='USERS';

INDEX_NAME PARTITION_NAME STATUS

PAR_IDX PAR1 USABLE PAR_IDX PAR2 USABLE

This way we can move a partitioned table having n number of partitions to different tablespace.

ORA-14511 파티셔닝 테이블 MOVE

DB CAFE