Category Archives: Lock

Find locks on a table

Posted on by
Reply

Using the step below one can find the locks on the table by looking for object id.

Example:
Window 1:
SQL> update scott.test1 set x = 200;

16 rows updated.

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
19

Window 2:
SQL> select object_id from dba_objects where object_name = ‘TEST1’ and owner = ‘SCOTT’;

OBJECT_ID
———-
13846

SQL> select * FROM V$lock where id1 = 13846;

ADDR KADDR SID TY ID1 ID2 LMODE
—————- —————- ———- — ———- ———- ———-
REQUEST CTIME BLOCK
———- ———- ———-
000000006934A5B0 000000006934A5D8 19 TM 13846 0 3
0 14775 0

Shows SID=19 has lock on the table.

Advertisements

Blocking locks

Posted on by
Reply

http://www.orafaq.com/node/854

SQL from the link above URL to find blocking SQL
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=’ || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

Output:
BLOCKING_STATUS
——————————————————————————–
SYS@localhost.localdomain ( SID=19 ) is blocking SYS@localhost.localdomain ( SID=21 )

Find the object being locked
SQL> SELECT vl.session_id, do.object_name FROM V$LOCKED_OBJECT vl, dba_objects do
WHERE vl.session_id IN (19, 21) and vl.object_id = do.object_id;

SESSION_ID OBJECT_NAME
———— ——————
19 TEST
21 TEST

How to resolve user locking problems

Posted on by
Reply

Method 1: Check and remove session locking other from Database

Find the USER locking others
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

Output:

USER SERIAL# SID USERNAME ID1 SQL_TEXT
13 23 ALIMA 393242 update authuser.emp set salary=1000

To Kill session
SQL>alter system kill session ’23,13’;
Session Killed.
Method 2: Check and remove by process id from Unix session, this query give you more details about the locking problem

To Kill session

On the Unix box
$ps -ef|grep username (i.e. alima)

alima 12102 12083 0 10:55:14 pts/3 20:98 sqlplus
alima 13011 12078 0 9:25:01 pts/5 0.12 -ksh

Process id is the first number column, determine which process id by matching the number

i.e. the process id to be killed is 12102
$kill -15 12102
$ps -ef|grep 12102

If the process id do not die
$kill -9 12102