"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:- <SOURCE LANG=SQL> 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 </SCOUCE>

Default tablespace of partitioned table :- <SOURCE LANG=SQL> SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION'; </SCOUCE> 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 :-

<SOURCE LANG=SQL> SQL> alter table partition modify default attributes tablespace users; </SCOUCE> Table altered. <SOURCE LANG=SQL> SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION'; </SCOUCE> TABLE_NAME PARTITIONING_TYPE DEF_TABLESPACE_NAME

PARTITION LIST USERS <SOURCE LANG=SQL> SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION'; </SCOUCE> TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 SYSTEM PARTITION PAR2 SYSTEM <SOURCE LANG=SQL> 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'); </SCOUCE> PL/SQL procedure successfully completed.

<SOURCE LANG=SQL> SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION' </SCOUCE> TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 SYSTEM 2 PARTITION PAR2 SYSTEM 2

Moving OLD partitions to different tablespace :-

<SOURCE LANG=SQL> 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'; </SCOUCE> 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 :-

<SOURCE LANG=SQL> SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM'; </SCOUCE> INDEX_NAME PARTITION_NAME STATUS

PAR_IDX PAR1 USABLE PAR_IDX PAR2 USABLE <SOURCE LANG=SQL>

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

</SCOUCE> Table altered. <SOURCE LANG=SQL> SQL> alter table PARTITION move partition PAR2 tablespace users parallel 10; </SCOUCE> Table altered. <SOURCE LANG=SQL> SQL> SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION'; </SCOUCE> TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

PARTITION PAR1 USERS 2 PARTITION PAR2 USERS 2 <SOURCE LANG=SQL> SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM'; </SCOUCE> 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. <SOURCE LANG=SQL> 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'; </SCOUCE>

'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; <SOURCE LANG=SQL> SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='USERS'; </SCOUCE> 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.