IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
Sometimes You can get “ORA-42009: error occurred while synchronizing the redefinition ” error.
Details of error are as follows.
ORA-42009: error occurred while synchronizing the redefinition
Cause: An error occurred while synchronizing the online redefinition.
Action: Correct the error and continue the online redefinition.
BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘”MEHMET”‘, ‘”TEST”‘, ‘”TEST$REORG”‘); END;
*
ERROR at line 1:
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (PIN01.I_SNAP$_TEST$REORG) violated
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 123
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1597
ORA-06512: at line 1
*
ERROR at line 1:
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (PIN01.I_SNAP$_TEST$REORG) violated
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 123
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1597
ORA-06512: at line 1
You can read the following post if you want to learn more detail about Online Redefinition.
Online Table Redefinition and Partitioning using DBMS_REDEFINITION in Oracle
This error is related with the data is located in bigfile tablespaces, this is the same issue as Bug 10033929 .
To solve this error, Download and apply patch# 12884105
Abort current redefinition of the table as follows.
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('MEHMET', 'TEST', 'TEST$REORG');
Then run the following sql.
SQL> Alter system set event = "10955 trace name context forever, level 256" scope=spfile;
Bounce the database and retry Online Redefinition again.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
