DB version: 19.18
OS: OL 8.6
I have a production DB whose application runs 24X7
I have a 'parent' table called SKU_MASTER and it has around 12 'child' tables.
For a purging activity, I had to disable foreign keys some of these child tables.
But, when I try to enable back the below-mentioned foreign key, the session is hung.
SKU_PLT_DTL table is 18 GB in size.
This is because my 24x7 application has row level lock using SELECT .... SKU_MASTER FOR UPDATE; on the parent table SKU_MASTER.
And both the application session and the session trying to enable the foreign key go to hung state.
Sometimes, a deadlock is detected and Oracle kills one of the sessions.
But, most of the time, I will have to press CTRL+C to cancel the FK enabling command and and the application session does it job very quickly (1 or 2 seconds) and all locks are released.
22:46:11 SQL> ALTER SESSION SET ddl_lock_timeout = 30;
Session altered.
Elapsed: 00:00:00.00
22:48:11 SQL> ALTER TABLE SKU_PLT_DTL enable constraint PLT_DTL_FK1;
^CALTER TABLE SKU_PLT_DTL enable constraint PLT_DTL_FK1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
SKU_PLT_DTL is 18 GB in size. I just need a 7 to 10 minute window to enable the foreign key in this table.
Will the following solution work ?
-- Lock the Parent table in exclusive mode
LOCK TABLE SKU_MASTER IN in exclusive mode;
ALTER SESSION SET ddl_lock_timeout = 30;
-- Now try to enable the FK
ALTER TABLE SKU_PLT_DTL enable constraint PLT_DTL_FK1;
And how will I revert the parent table SKU_MASTER to the normal mode ?
Or are there any other workarounds ?