파티셔닝된 테이블의 테이블 스페이스를 옮기려고 할 때 발생했던 오류.
SQL> alter table 테이블명 move tablespace 테이블스페이스명
이런경우에는 아래와 같은 명령어를 사용하자.
SQL> alter table 테이블명 modify default attributes tablespace 테이블스페이스명;
그리고 부가적으로 처리해야 하는 일들이 좀 있다.
자세한 사항은 아래 영문을 확인해보길 바란다.
출처 : http://amit7oracledba.blogspot.kr/2013/03/move-partitioned-tables-to-different.html
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.
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.