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.

I hope this article helped you.

Regards,
Amit Rath