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.sqlAfterwards, 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; exitExecut the script:
sqlplus / as sysdba @drop_empty_part.sqlThe 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 :-)
