Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Identify Hanging Sessions

Status
Not open for further replies.
fatcodeguy

fatcodeguy

Programmer
Feb 25, 2002
281
CA
Hi,

Is there a way to identify is a session is hanging/locked up. I had a script for 8i but the views seem to have changed.

Thanks for the help!!
 
Sort by date Sort by votes
Fat,

When you say, "Is there a way to identify is a session is hanging/locked up", do you mean hanging/locked up by virtue of another session holding a "blocking lock"? If so, then yes, I have a script.

Let me know if this is what you meant.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
@ 19:41 (23Feb05) UTC (aka "GMT" and "Zulu"),
@ 12:41 (23Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Upvote 0 Downvote
Or else post what you had for 8i; we may be able to beat it into compliance with 9i.
 
Upvote 0 Downvote
Hey,

Here's what I had for 8i
Code: 
select ses.sid,
	   ses.serial#,
	   ''''||ses.sid||','||ses.serial#||'''' session_kill,
	   decode(command,0,'None',2,'Insert',3,'Select',6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',45,'Rollback',47,'PL/SQL',command) command,
   	   ses.osuser,
	   ses.username,
	   ses.program,
  	   event,p1,p2,p3,state,
	   wait.wait_time
from   v$session ses, v$session_wait wait
where  ses.sid=wait.sid
and    (ses.sid>10 and event not in('SQL*Net message from client','SQL*Net message to client')
or 	   (ses.sid<=10 and event not in ('rdbms ipc message','smon timer','pmon timer','SQL*Net message from client')))
order by decode(event,'pipe get','A',event),p1,p2

If p1,p2, and p3 values were not changing, it was "hanging". I got it from
Mufasa, I'm not sure why it's hanging locked up. I had a session that was running a process and it's stopped midway (i know because it writes logs to a table that I can verify). Could you post your script anyway, I'd like to look at it.

Thanks!!
 
Upvote 0 Downvote
Fat,

I ran your code on my 9.2.0.4 system. I did not change any code...I just reformatted for display purposes, with these results:
Code: 
SID                                   Oracle                                                                          Wait
Ser        Command    OSUser          User            Program         Event                       State               Time
---------- ---------- --------------- --------------- --------------- --------------------------- ----------------- ------
'7,176'    Select     TENFOLD\dhunt   DHUNT           sqlplusw.exe    SQL*Net message to client   WAITED KNOWN TIME     -1
**************************************************************************************************************************

My "LockBlock.sql" script, below, may look a bit contrived since it creates temporary tables instead of relying soley on views. I elected to create temporary tables to allow indexing (which is not available on views). Without indexing, identifying blocking locks for large installations can take an inordinate amount of time:
Code: 
Rem    Name:    LockBlock.sql
Rem    Author:  Dave Hunt
Rem
Rem    This script shows who is holding a lock that other people are
Rem    waiting for and who the waiters are.
Rem
Rem
prompt
prompt Gathering lock information...
set echo off
set feedback off
set pagesize 0
set linesize 150
ttitle off
col "Object" format a20
col "Holder" format a25
col "Waiter" format a25
col "Lock Type" format a20
prompt ...Countdown...8...
create table TempAllObjects as select * from All_Objects;
prompt ...7...
create index TempAllObjectsID on TempAllObjects (object_id);
prompt ...6...
create table TempVSession as select * from v$Session;
prompt ...5...
create index TempVSessionSID on TempVSession (sid);
prompt ...4...
create table TempVLock as select * from v$lock;
prompt ...3...
create index TempVLockSID on TempVLock(SID);
prompt ...2...
create index TempVLockID1 on TempVLock(ID1);
prompt ...1...
create index TempVLockType on TempVLock(Type);
prompt GO
prompt
select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',
           'There are '||count(*)||' blocking locks:')
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
set pagesize 35
select distinct o.owner||'.'||o.object_name "Object"
   ,sh.osuser||':'||sh.username||'('||sh.sid||')' "Holder"
   ,sw.osuser||':'||sw.username||'('||sw.sid||')' "Waiter",
        decode(lh.lmode
           , 0, 'none'
           , 1, 'null'
           , 2, 'row share'
           , 3, 'row exclusive'
           , 4, 'share'
           , 5, 'share row exclusive'
           , 6, 'exclusive'
           ,    'unknown')  "Lock Type"
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
drop table TempAllObjects;
drop table TempVSession;
drop table TempVLock;
prompt
set feedback on

SQL> @lockblock

Gathering lock information...
...Countdown...8...
...7...
...6...
...5...
...4...
...3...
...2...
...1...
GO

There is 1 blocking lock:

Object               Holder                    Waiter                    Lock Type
-------------------- ------------------------- ------------------------- --------------------
TEST.S_REGION        TENFOLD\dhunt:TEST(7)     TENFOLD\dhunt:DHUNT(8)    row share

Let us know if you have questions or follow-on issues.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
@ 18:27 (24Feb05) UTC (aka "GMT" and "Zulu"),
@ 11:27 (24Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Upvote 0 Downvote
Hi,

sorry for delay in reply, I was away.

Mufasa, thanks for your script, i'm sure it will come in handy. When I run my script, I get a
Code: 
ORA-00942: table or view does not exist
error on view v$session_wait wait.

Any suggestions?
 
Upvote 0 Downvote
Yes, Fat, that error (besides meaning the obvious...that it really doesn't exist) could mean that you are logged into an Oracle user account that does not have permission to access that object. Since v$session_wait is a DBA Data Dictionary view, you need to have either the "SELECT ANY TABLE" privilege or be a member of the "select_catalog_role".

So, before we do any additional remote trouble shooting, you need to try your code while logged in as a DBA-privileged user, or you need to otherwise have enough SELECT privileges to access the data-dictionary views.

Let us know what you find.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:06 (02Mar05) UTC (aka "GMT" and "Zulu"),
@ 09:06 (02Mar05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
920
Andrzejek
Andrzejek
jasonhuibers
  • Locked
  • Question
Identify records that are non ascii character
Replies
5
Views
2K
Dagon
Dagon
SMS636
  • Locked
  • Question
How to identify session killing application sessions
Replies
0
Views
92
SMS636
SMS636
adamsmart222
  • Locked
  • Question
how to identify a real Oracle Programer?
Replies
1
Views
67
Andrzejek
Andrzejek
helpdbas
  • Locked
  • Question
kill sessions using loop 1
Replies
13
Views
738
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top