In my case, I was redefining an incorrectly partitioned table. The interim table was also partitioned, but this time correctly. During the phase where I call the COPY_TABLE_DEPENDENTS procedure to transfer the existing dependant objects (triggers, indexes, constraints etc) from the original table to the interim table, it failed with the message
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table ORA-06512: at "SYS.DBMS_REDEFINITION", line 2761This was because I was using the directive dbms_redefinition.cons_orig_params in my code:
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SALES',
orig_table=>'DAILYSALES',
int_table=>'DAILYSALES_INTERIM',
copy_indexes=>dbms_redefinition.cons_orig_params,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
END;
/
As such, the redefinition process attempted to transfer the partitioned index from the original table which had a different partitioning scheme, to the new table who was partitioned differently and had fewer partitions.
As a workaround, I recreated the index directly on the interim table:
CREATE INDEX SALES.SEARCHIX1_INTERIM ON SALES.DAILYSALES_INTERIM (COL1, COL2 DESC) TABLESPACE DATA2 LOCAL PARALLEL ( DEGREE 5 INSTANCES 1 );Then, "plug it into" the redefinition procedure:
exec DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SALES','DAILYSALES','DAILYSALES_INTERIM',2,'SALES','SEARCHIX1','SEARCHIX1_INTERIM');Finally, change the directive in your COPY_TABLE_DEPENDENTS procedure from
copy_indexes=>dbms_redefinition.cons_orig_paramsto
copy_indexes=>0And run the COPY_TABLE_DEPENDENTS procedure again.
When done, finish the redefinition by calling the FINISH_REDEF_TABLE procedure, and you'll see that the interim index on the interim tables has been nicely transfered during the switch.
The Oracle 18c documentation can be found here
Another good source is HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (Doc ID 1304838.1 from Oracle Support)
No comments:
Post a Comment