PROBLEM:

While doing online redefinition of partitioned table, got ORA-012024 error.

SQL> DECLARE
2 error_count pls_integer := 0;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('RAJDBA', 'SOC_TABLE', 'SOC_TABLE_INT', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
5 DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
6 END;
7 /

DECLARE
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the
underlying table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2058
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2583
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3956
ORA-06512: at line 4

SOLUTION:

Case 1 :

First check whether your original table and interim table has same number of table partitions.

select count(*) from dba_tab_partitions where owner='RAJDBA' and table_NAME='SOC_TABLE';

select count(*) from dba_tab_partitions where owner='RAJDBA' and table_NAME='SOC_TABLE_INTERIM';

If the interim table has less partition, then create the missing partitions in the interim table by referring the original table.

Now retry the redef operation. It should work.

Case 2:

 

If the table is interval partitioned and the oracle version is 12.1.2, then even if both the original and interim table has same number of partitioned, you might face the same error.

To fix this, you need to apply the bugfix: 28487821

 

Apply the patch:

/dumparea/PATCH_REDEF/28487821$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/oracle/product/12.1.0.2/dbhome_1
Central Inventory : /gridapp/app/oraInventory
   from           : /oracle/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2020-11-20_09-47-29AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   28487821

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '28487821' to OH '/oracle/app/oracle/product/12.1.0.2/dbhome_1'

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patch 28487821 successfully applied.
Log file location: /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2020-11-20_09-47-29AM_1.log

OPatch succeeded.



$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Fri Nov 20 11:17:09 2020
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3760_2020_11_20_11_17_09/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 28487821 (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS FAILS WITH ORA-14024):
  Not installed in the binary or the SQL registry
Patch 28487821 (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS FAILS WITH ORA-14024):
  Installed in the binary registry only

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    28487821 (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS FAILS WITH ORA-14024)

Installing patches...


Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 28487821 apply: SUCCESS
  logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/28487821/23531879/28487821_apply__2020Nov20_11_23_19.log (no errors)
SQL Patching tool complete on Fri Nov 20 11:23:20 2020

Once the patch is applied, try the redef process.

SQL> DECLARE
  2     error_count pls_integer := 0;
  3  BEGIN
  4      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('RAJDBA', 'SOC_TABLE', 'SOC_TABLE_INT', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
  5      DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
  6  END;
  7  /


SQL> SQL> SQL>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

Voilla. No errors. 🙂

Reference: metalink – Doc ID 1961587.1