Who is locking with what type of lock ?

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’
) REQUEST,
DECODE( A.lmode,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
) LMODE,
DECODE (A.BLOCK,
0, ‘None’ ,
1, ‘BLOCKING’) BLOCK,
B.os_user_name,
C.object_name
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