| Hung Session [message #221896] |
Wed, 28 February 2007 21:20  |
Tommy77
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  |
nmacdannald
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
