Home » RDBMS Server » Server Administration » Hung Session
Hung Session [message #221896] Wed, 28 February 2007 21:20 Go to next message
Messages: 2
Registered: February 2007
Junior Member
We have Oracle 10gR2 database and we ran into issue when all the session were hang in the database. I found the two blocker process using following queries and then killed both of them but it didn't do any good in DB and problem still persists so I had to recycled DB to resolve this issue. My question is that is there any other way to problematic session which was causing issue. I even used to Grid Control but couldn't found anything

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 ;

SQL> select object_name, v.session_id SID, v.oracle_username,type,lmode,request from gv$locked_object v, gv$lock l, dba_objects o where l.sid=v.session_id and v.object_id=o.object_id and l.block > 0;

To identify who locks whom :

SQL> select (select osuser from v$session where sid=a.sid) blocker,
a.sid, (select serial# from v$session where sid=a.sid) serial#,
' blocks ', (select osuser from v$session where sid=b.sid) blockee,
b.sid, c.username username from v$lock a, v$lock b, v$session c
where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2 and b.sid = c.sid;
Re: Hung Session [message #223416 is a reply to message #221896] Thu, 08 March 2007 16:45 Go to previous message
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
I have found in Oracle Applications that you really only need kill one session, then the other blocker finishes. Sorry I can not tell which one...
I usually kill the oldest one.

kill -9 <process id> at the o/s prompt (Solaris)
Previous Topic: how to configures iSQL*PLUS (merged)
Next Topic: Command to know which process is doing huge paging
Goto Forum:

Current Time: Fri Oct 28 00:09:55 CDT 2016

Total time taken to generate the page: 0.21970 seconds