In passing, there were some horrendous errors with referential integrity
constraints and foreign key locking in the 10.2/11.1 timeframe when you disable
table lock on the child table. (See:
https://jonathanlewis.wordpress.com/2010/02/15/lock-horror/ ;) I haven't tested
the code recently but one of the comments said the behaviour was still present
in 11.2.0.1 - you might want to test for 11.2 or 12.1
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
Sent: 31 January 2017 22:18:24
To: ORACLE-L
Subject: TIL: Re-enabling locks on any one table requires NO DML activity
anywhere in the DB
This caught me completely off guard today. Maybe I should have known, but
Oracle requires NO DML activity occurring anywhere in the DB to re-enable table
locks on any one table.
That really surprised me since if the table being operated on has DISABLED
LOCKS, then how does other locking activity in the DB somehow interfere?
The support document says there's now no way to know if anyone is updating the
table... but if you disabled locking, then how could that even happen?
From the Support site:
Doc ID 857975.1
The session attempting to enable the table lock must wait until ALL active DML
transactions in the database have completed before locking the table. Once the
DISABLE TABLE LOCKS feature has been used, then you stop Oracle from taking out
ANY TM locks when the table is updated. If the user tries to ENABLE table
locking again, Oracle has no way of knowing if anyone is actually updating the
table at this time (because there are no TM locks!). The only solution is to
wait for all active transactions to complete. This is expected behavior and not
a bug. This is documented in the following manual:
To re-enable table locks you need to ensure there are no current active DML
transactions in the database by other users. The easiest way to achieve this is
to shutdown the database and startup it in restricted mode. Then, try to enable
the locking.
--
//www.freelists.org/webpage/oracle-l
