Coskan’s Approach to Oracle

February 6, 2007

Who is locking with what type of lock ?

Filed under: How To — coskan @ 12:54 pm

Some of our main application processes hanged today (not the database only the processes) while i was at lunch time. When I arrived the company my manager asked me to look at the database for understanding the cause of hang. I simply realised that there is a lock on somewhere which is waiting other sessions.

I followed the following way to understand what was going on for solving the problem.

1- who is blocking
select holding_session from dba_blockers;

2- what is he blocking

select sid,serial#,sql_text,username from v$sql s,v$session se where se.sql_id=s.sql_id where sid=’holder’;

3- when i looked in the sql_text i understood session is working on table X

after that i looked at what is the type of lock for being sure about the cause of the problem.

/*who is locking ? what type of lock ?*/

select A.sid,A.type,DECODE (A.REQUEST,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
DECODE( A.lmode,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
0, ‘None’ ,
from v$lock A, v$locked_object B, dba_objects C
where B.session_id=A.sid and C.object_id = B.object_id
AND OWNER=’OWNER’ and c.object_name=’X;

4- The session was one of the DBA’s session so the session could be killed and i killed the session

alter system kill session ‘sid,serial#’

5-I looked back to blockers table and became sure that the problem is solved.

select holding_session from dba_blockers;

>no rows


1 Comment »

  1. Thanks for sharing this information. Really is pack with new knowledge. Keep them coming.

    Comment by Great Home Improvement Info — August 17, 2007 @ 12:57 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: