ORA-14257: cannot move a partition which is a composite partition | Move Table, Partition, Subpartitions to New Tablespace

Hi,

I got ” ORA-14257: cannot move a partition which is a composite partition ” error during table or partition moving.

 

Details of error are as follows.

ALTER TABLE MSDBA.TEST_TABLE_2019121214_3 MOVE PARTITION ORAOOP_20191212_155611 TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES parallel 96
Error at line 1
ORA-14257: cannot move a partition which is a composite partition

ALTER TABLE MSDBA.PREPAID_VOICE_EDR_2019110821_8 MOVE PARTITION ORAOOP_20191108_223134 TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES parallel 96
Error at line 2
ORA-14257: cannot move a partition which is a composite partition

 

 

 

This error is related with the Composite PARTİTİONS.

 

If you don’t know what is the Partitioning and its types, read the following post to learn it more detailed.

Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database

 

If your tables are composite partitioning, then you need to move the sub-partitions firstly instead of the partitions.

 

To solve this error, modify tables’ attributes for partition as follows.

alter table [table_name] modify default attributes for partition [partition_name] tablespace [tablespace_name];

ALTER TABLE MSDBA.TEST_TABLE_2019121214_3 modify default attributes for partition ORAOOP_20191212_155611 tablespace new_tablespace_name;

 

 

You can use the following generate script for modifying tables’ attributes for partition.

 

 SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE TBS_REENGENERING_RSTG;' FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME='REENGINNERING_TBS';

 

 

After you modify tables’ attributes for partition, then , move the subpartitions as follows.

ALTER TABLE MSDBA.TEST_TABLE_2019121214_3  MOVE SUBPARTITION SYS_SUBP2802660 TABLESPACE NEW_TABLESPACE_NAME parallel 96 UPDATE INDEXES;

 

You can generated the subpartition moving with the following script.

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE NEW_TABLESPACE_NAME parallel 64 UPDATE INDEXES;' FROM DBA_TAB_SUBPARTITIONS where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';


SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE NEW_TABLESPACE_NAME parallel 64 UPDATE INDEXES;' FROM DBA_TAB_SUBPARTITIONS where TABLE_OWNER = 'SCHEMA_NAME';

 

Above script will generate the bulk move scripts, copy them and paste new editor and run them to move objects.

 

Then secondly move the partitions after subpartitions moving.

ALTER TABLE MSDBA.TEST_TABLE_2019121214_3 MOVE PARTITION ORAOOP_20191212_155611 TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES parallel 96;

 

You can generated the Partition moving with the following script.

select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES parallel 96;' from dba_tab_partitions where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

Above script will generate the bulk move scripts, copy them and paste new editor and run them to move objects.

 

You can move the indexes as follows.

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'new_tablespace online parallel 8;' from dba_indexes where tablespace_name='OLD_TABLESPACE_NAME';

Above script will generate the bulk move scripts, copy them and paste new editor and run them to move objects.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )