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