How to find and remove table level lock in Oracle

Dear Blog readers,

Today let's see how to find and remove the table level lock in Oracle and in what case we would require to do so.

An application developer contacted me that they ran deletes on a particular table and it was probably ran by multiple sessions at the same time and due to huge number of records, there was a lock.

Application team basically wanted to clean up the whole table and ran:

delete from "OWNER"."MYTABLE_NAME";

.......

In this case they should have cleverly done a truncation instead of deletes. We all know delete is a DML and truncate is a DDL which is much more faster and also resets the HWM (High Water Mark).

Ok lets see what I did here,

SQL> alter session set ddl_lock_timeout=30 ;

Session altered.

SQL> truncate table "OWNER"."MYTABLE_NAME" drop storage;
truncate table "OWNER"."MYTABLE_NAME" drop storage
                                     *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Since the deletes are still running against the table and has put a lock, we will not be able to perform a DDL on it.

Let's find a release the lock.

SQL> select a.sid||'|'|| a.serial#||'|'|| a.process
 from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
 and a.sid = b.session_id
 and OBJECT_NAME=upper('&TABLE_NAME'); 

Enter value for table_name: MYTABLE_NAME
old   5:  and OBJECT_NAME=upper('&TABLE_NAME')
new   5:  and OBJECT_NAME=upper('MYTABLE_NAME')

A.SID||'|'||A.SERIAL#||'|'||A.PROCESS
-------------------------------------
49|45509|6236
49|45509|6236
49|45509|6236
49|45509|6236

Kill the session which has locked the table.

SQL> alter system kill session '49,45509' immediate;

System altered.

Now the system will allow you to truncate the table.

SQL> truncate table "OWNER"."TABLE_NAME" drop storage;

Table truncated.

Let's also see other scenarios which may help you in your finding.

Scenario 1: To find sid, serial# and process of locked object

select a.sid||'|'|| a.serial#||'|'|| a.process
 from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
 and a.sid = b.session_id
 and OBJECT_NAME=upper('&TABLE_NAME'); 

Note: if you don't have dba_objects privilege replace it by user_objects. In RAC use GV$locked_object

Scenario 2: To find process holding the lock by passing table name.

select distinct a.process
 from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
 and a.sid = b.session_id
 and OBJECT_NAME=upper('&TABLE_NAME');

Scenario 3: To find blocking locks into the database.

select 
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

Scenario 4: To find blocking session and type of lock.

select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
 from gv$lock l1, gv$lock l2
 where l1.block =1 and l2.request > 0
 and l1.id1=l2.id1
 and l1.id2=l2.id2; 

Scenario 5: To get the detailed information in RAC.

SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
     FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND
     s1.inst_id=l1.inst_id AND
     s2.sid=l2.sid AND
     s2.inst_id=l2.inst_id AND
     l1.BLOCK=1 AND
    l2.request > 0 AND
    l1.id1 = l2.id1 AND
    l2.id2 = l2.id2 ;

Hope this blog helps you. Thank you.

Regards,

Balaaji Dhananjayan