In my previous articles i have given so many real life SQL scenarios in which are useful. In this article i would like to give answer of very common question-How to remove oracle table lock? with examples. There are so many times DBA will get the request to remove oracle table lock in frequent basis.
How to remove oracle table lock? Step by Step
Table lock is very commonly used concept in Oracle database where we require to use the system views and specifically v$lock view. When one user is using the specified table and at the same time other user wants to work on the same table he might get the error as table is locked or sometimes some update is taking too much time and deadlock will occur and oracle system locks the table. At that time DBA needs to act and need to release lock by killing the sessions which are holding the lock.
Query 1 : To check the Oracle Locks
The first step is always to check the oracle locks. here question is how to check the oracle locks? You might have to know about the
The first step is to find out the sid,serial number and the process how the table or object has locked. The standard query to check that process ,
Syntax :
select a.sid as "SID" ||'&&'|| a.serial# as"Serial Number" ||'&&'|| a.process from v$session p, v$locked_object q, dba_objects r where q.object_id = r.object_id and p.sid = q.session_id and OBJECT_NAME=upper('&TABLE_NAME');You just need to add the table name and fetch the SID and Serial number and process which is blocking the table.
If you dont have dba_objects privilege then you need to use the GV$locked object.
Query 2 : You can find out the process of holding the lock
Query to find process information :
The below query will give the data of the process which is locking the object,
select distinct p.process as "Process_Data" from v$session p, v$locked_object q, dba_objects r where q.object_id = r.object_id and p.sid = q.session_id and OBJECT_NAME=upper('TABLE_NAME');Query 3 : To check and find out the blocking locks in the database
You need to find out the blocking locks in oracle database.
Query 1 :
You can simply use following query to fetch the SID,
select
session_id
from
dba_dml_locks
where
name = ‘TABLE_NAME’;
Or you can use following query to fetch the blocker session.
Query 2 :
select
(select username from v$session where sid=a.sid) blocker_Session,
p.sid, ‘ is blocking ‘,
(select username from v$session where sid=b.sid) blockee_session,
q.sid from
v$lock p, v$lock q where
p.block = 1 and q.request > 0
and p.id1 = q.id1
and p.id2 = q.id2;
Query 4 : What is query to find blocking session and the lock time.
Query :
select P.inst_id,P.sid, ‘ SID_Of_Blocking_Session ‘, Q.sid,P.type,Q.type,P.lmode,Q.lmode,Q.inst_id
from gv$lock P,
gv$lock Q
where P.block =1 and Q.request > 0
and P.id1=Q.id1 and P.id2=Q.id2;
Query 5 : How to get detailed information about RAC?
Query :
SELECT ‘Instance_ID’||s1.INST_ID||’ ‘|| s1.username || ‘@’ || s1.machine || ‘ ( SID=’ || s1.sid || ‘,’|| s1.serial#||s1.status|| ‘ ) Blocking Session ‘ || 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 ;
Scenario for Blocking session and Killing it :
If you get information about blocking session for specific query or table and we require to remove locks then use following queries. Lets say that there is employee table and we require to check why that table is locked,
Step 1 : Find out the session id of query
Query :
select
session_id As “Session ID”
from
dba_dml_locks
where
name = ‘Employee’;
Session_ID
445
Step 2 : Find Serial Number using following Query
select
sid,
serial#
from
v$session
where
sid in (
select
session_id
from
dba_dml_locks
where
name = ‘Employee’);
Session_ID Serial#
445 445434
Step 3 : To use Alter Statement to kill session
alter system kill session ‘SID,SERIALl#’;
alter system kill session ‘445,445434’;
The above query is used to kill the sessions. This is the correct way of How to remove Oracle locks? If you like this article or if you have any concerns with the same kindly comment in comments section.
Some Important queries to Remember :
To find SQL_ID from SID:
select sql_id from v$session where sid=&sid;
To find SQL Query with taking help from SQL_ID :
select sql_fulltext from gv$sql where sql_id =”&SLQ_ID”;