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);
Recent Posts