ORA-14511: cannot perform operation on a partitioned object

I got ” ORA-14511: cannot perform operation on a partitioned object” error in Oracle database.

 

ORA-14511: cannot perform operation on a partitioned object

 

Details of error are as follows.

SQL> alter table MSDBA.TEST_TABLE move parallel 32;
alter table MSDBA.TEST_TABLE move parallel 32
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object


Elapsed: 00:00:00.17
SQL>

 

 

The ORA-14511 error is related with the Partitioned table moving.

If your table is partitioned, then you need to move partitions.

 

ORA-14511

To solve this error, you should move the partitions of table as follows.

 

alter table MSDBA.TEST_TABLE move partition P1 parallel 32;

 

 

cannot perform operation on a partitioned object

You can generate for all partitions movins using the following scripts.

select 'alter table '||table_owner||'.'||table_name||' MOVE PARTITION '||partition_name||' parallel 32;' from dba_tab_partitions where table_name='TEST_TABLE' and table_owner='MSDBA';

 

When you Run the above code, it will generate the following scripts, execute the following scripts to solve problem.

alter table MSDBA.TEST_TABLE move partition P1 parallel 32;
alter table MSDBA.TEST_TABLE move partition P2 parallel 32;
alter table MSDBA.TEST_TABLE move partition P3 parallel 32;
alter table MSDBA.TEST_TABLE move partition P4 parallel 32;
alter table MSDBA.TEST_TABLE move partition P5 parallel 32;
alter table MSDBA.TEST_TABLE move partition P6 parallel 32;
alter table MSDBA.TEST_TABLE move partition P7 parallel 32;
alter table MSDBA.TEST_TABLE move partition P8 parallel 32;

 

If you are moving the partition different tablespace, then you can use the following scripts.

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

 

 

 

 

Do you want to learn Oracle SQL, then Click This Link and read the articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

See more