How to solve ORA-14024 during execution of DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

Background: you are redefining a table and have successfully completed the DBMS_REDEFINITION.START_REDEF_TABLE procedure.

As you are executing the next step, DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, you receive the following error:

ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Cause: Your interim table has been created with more partitions than the original table you're trying to redefine.

Verify like this:

select 'original table: ' || MAX(PARTITION_POSITION) "number of partitions"
from dba_tab_partitions where table_name='SALES' 
union
select 'interim table: ' || MAX(PARTITION_POSITION) 
from dba_tab_partitions where table_name='SALES_INTERIM' 
;

number of partitions
interim table: 19
original table: 18

A simple solution here is avoiding to create the indexes as a part of the COPY_TABLE_DEPENDENTS, and create them afterwards instead.

Simply change the directive

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

when you execute DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.

When you're done redefining your table, make sure you recreate the indexes.

If this is not acceptable, you need to add another partition to your original table, so that the missing index partitions will be automatically created.


Update 21.06.2021: I faced a similar challenge today as I was trying to redefine a table with interval range partitioning. As I was testing my redefinition strategy in a test environment first, the table in question had 46 partitions with zero rows. This fact raised ORA-14024: number of partitions of LOCAL index must equal that of the underlying table, and I realized I could quite easily work around the problem by simply dropping the empty partitions. To be safe you drop any partitions that actually had rows in them, analyzed them first:

set lines 200
spool analyze_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool analyze_part.log' from dual;
select 'exec DBMS_STATS.GATHER_TABLE_STATS (OwnName=> ''TRACKER'', TabName => ''EVENTS'',partname =>''' || partition_name || ''' granularity => ''PARTITION'';' 
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';

Run the analyze-script:

sqlplus / as sysdba @analyze_part.sql

Afterwards, the num_rows column for the same partitions should show 0 (zero) rows. In other words, they can be safely dropped.

To generate a "drop partition" script, put the following in an sql-script:

set lines 200
spool drop_empty_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool drop_empty_part.log' from dual;

select 'ALTER TABLE ' || table_owner || '.' || table_name ||  ' DROP PARTITION ' || PARTITION_NAME ||  ' UPDATE INDEXES;'
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';
select 'exit' from dual;
exit

Execut the script:

sqlplus / as sysdba @drop_empty_part.sql

The number of partitions in the original table and the interim table should now match. Now, execute the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS again. You shouldn't see the ORA-14024 this time around :-)