sessions script

rem ********************************************************************
rem Filename : showSessions.sql
rem Version : 1.01
rem Usage : SQL> @showSessions
rem Description : List all current user sessions and their SQL
rem
rem Author : Andrew Morris
rem Date : 17 May 98
rem Notes:
rem O/S Usr - Operating System Username for backend server process.
rem Srvr PID - O/S backend server process id.
rem Clint PID - O/S client process id (for local connections) or
rem - some sort of SQL*Net address (for remote connections).
rem SID_SERIAL - Session ID, Session Serial Number (reqd for kill session command).
rem TERMINAL - Terminal Name (if interactive session otherwise null).
rem Ora User - Oracle username.
rem STATUS - Session status ie Active, Inactive or killed.
rem Logon Time - Date & Time session first connected.
rem Idle - Hours & minutes since the session sent any SQL.
rem for active sessions this is when the current sql was submitted
rem for inactive session this is the amount of time since the last sql
rem LkSID - SID for the blocking session. ie the session that has obtained a
rem lock that the current session must wait for before continueing.
rem Wait - Hours & Mins that the session has been waiting for the lock to be
rem released.
rem
rem Modified
rem Date Author Reason
rem ********************************************************************
set pause ...more
set pagesize 24
set linesize 132
set numwidth 5
column SID_Serial format a10
column Idle format a5
column LkMins format 999
column Wait format a5

select substr(vp.username, 1, 8) "O/S Usr", vp.spid "Srvr PID", vs.process "Clint PID",
vs.sid||','||vs.serial# SID_Serial,
vs.terminal, substr(vs.username, 1, 10) "Ora User",
vs.status, substr(to_char(vs.LOGON_TIME, 'dd/mon hh:mi'), 1, 12) "Logon Time",
to_char(trunc(sysdate) + (vs.LAST_CALL_ET/(24*3600)), 'hh24:mi') "Idle",
vl2.sid "LkSID",
to_char(trunc(sysdate) + (vl1.ctime/(24*3600)), 'hh24:mi') "Wait"
from V$PROCESS vp, V$SESSION vs, V$LOCK vl1, V$LOCK vl2
where vp.addr = vs.paddr
and vs.lockwait = vl1.kaddr(+)
and vl1.id1=vl2.id1(+)
and vl2.lmode(+)!=0
and vs.paddr not in (select paddr from V$BGPROCESS) -- exclude background processes
order by 7, 9 -- Status, Idle Time
/

set pause on
col module format a20
break on SID_Serial SKIP 1 on status

select
vs.sid||','||vs.serial# SID_Serial,
vs.status, vs.module, st.sql_text
from V$SESSION vs, V$SQLTEXT st
where st.address=decode(vs.sql_address, hextoraw('00'), vs.prev_sql_addr, vs.sql_address)
and st.hash_value=decode(vs.sql_hash_value, 0, vs.prev_hash_value, vs.sql_hash_value)
and vs.paddr not in (select paddr from V$BGPROCESS) -- exclude background processes
order by vs.status, vs.last_call_et, 1, st.piece
/

set pause off
set numwidth 10
clear columns
clear breaks