The point is that ANY active transaction MAY have uncommitted changes on the
table in question and the enabling session won't be able to see them because TM
locks on the table won't have been created, so the session trying to enable
locking has to wait for all current transactions to commit so that it knows
there are no active transactions on the table. (Session which try to modify
the table after the enable starts will wait on a KGL Lock, so won't introduce
new waits - but if they had a transaction already active they're effectively in
a deadlock and will wait until the enabler times out.
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
