Hello Team,
I encountered ora-04024 during online redefinition of a table. The entire database hangs when i execute dbms_redefinition.finish_redef_table package.
Below are the summary of steps
1. create interim table TABLE_PART
2. Create indexes on TABLE_PART
3. Enable row movement
4. BEGIN
DBMS_REDEFINITION.start_redef_table(uname =>'user',orig_table =>'TABLE',int_table => 'TABLE_PART');
END;
/
5.
BEGIN
dbms_redefinition.sync_interim_table(uname => 'USER',orig_table => 'TABLE',int_table => 'TABLE_PART');
END;
/
6.
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'USER',
orig_table => 'TABLE',
int_table => 'TABLE_PART',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
7. Gather statistics on TABLE_PART
8. Rename original table
SET SERVEROUTPUT ON;
BEGIN
dbms_redefinition.finish_redef_table(uname => 'user',orig_table => 'TABLE',int_table => 'TABLE_PART');
END;
/
During #8 dbms_redefinition.finish_redef_table the database hangs and ORA-04024 is shown in the alert log:
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x14EE45E890
Database version is
12.1.0.2 (RAC 2 Nodes) on Solaris 11.3 Sparc
Kindly help .
thank you.