DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

To check for Long Running Sessions


SESSIONS ACTIVE FOR MORE THAN AN HOUR:

set pagesize 5000
set lines 150
col username for a15
col osuser for a15
col program for a20
col logon_time for a20
col status for a8
col machine for a15
col sql_text for a100
col EVENT for a30
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
col p1 for 9999999999999
col p2 for 9999999999999
col p3 for 9999999999999
col LAST_CALL_ET_HRS for 999999.99

select sid,serial#,username,osuser,program,machine,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",last_call_et/3600 " LAST_CALL_ET_HRS" from v$session where status='ACTIVE' AND username is not null and last_call_et/3600 >1 order by 9 desc;


SQL_TEXT OF LONG RUNNING SESSIONS:

select sid,sql_text from v$sqltext , v$session where v$sqltext.address = v$session.sql_address and sid in (select sid from v$session where status='ACTIVE' AND username is not null and last_call_et/3600 >1) order by sid,piece;

WAIT_EVENT OF LONG RUNNING SESSIONS:

select sid,EVENT,P1TEXT,P1,P2TEXT,P2,P3TEXT,P3,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where sid in (select sid from v$session where status='ACTIVE' AND username is not null and last_call_et/3600 >1);

Active transaction

select username,s.sid,
t.used_ublk,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr and
s.sid= '&sid' ;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid='&sessionid';

Checking rollback/Undo segment info used by SID

column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr, nvl(s.username,'no transaction') us,s.sid, s.osuser os, s.terminal te, rs.rssize,    
rs.xacts, rs.rssize/1048576 Rssize
FROM v$lock  l, v$session  s,v$rollname  r , v$rollstat rs
WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND
l.lmode = 6   AND r.usn=rs.usn  and s.sid in (&sid_list_comma_sep);

No comments: