CODEWITHSUNDEEP
oracle-databasealter
Piotr Adamczyk
Piotr Adamczyk

Reputation: 23

Oracle - ALTER TABLE ENABLE TABLE LOCK

I did ALTER TABLE DISABLE TABLE LOCK on one of our tables and now I can't enable table lock back. When I'm executing on this table:

ALTER TABLE x ENABLE TABLE LOCK;

I'm getting:

SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I checked that there was a blocking session from other user but he killed his session and now I still can't enable table lock. There is new locking session, looking like some system session (OSUSER=SYSTEM, PROGRAM=ORACLE.EXE (DIA0), TYPE=BACKGROUND).

Could you please help me with this enable table lock?

Edit: After database restart we were able to enable table lock.

Upvotes: 0

Views: 14075

Answers (1)

Sebz
Sebz

Reputation: 502

Run this query to identify the sessions that are locking your object:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

With results from this query you can run ALTER SYSTEM KILL SESSION 'sid,serial#'; that should clear the locks and you can retry acquiring the lock.

If you are having a process that re-spawns and acquires the lock before your session you can, you can write a while loop that checks if the lock can be acquired by catching the ORA-00054 error and retrying until the lock can be placed using LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;

Upvotes: 0

Related Questions