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