--
-- Show information on all database sessions
--
-- Displays database sessions together with their current SQL, wait state
-- and blocking relationships.
--
-- Blocking sessions are shown above the sessions they are blocking,
-- producing a simple blocking tree.
--
-- Useful for:
-- * Investigating application/database hangs
-- * Finding blocked sessions
-- * Identifying the root blocker in a blocking chain
-- * Seeing what SQL an active session is executing
-- * Distinguishing active, idle and waiting sessions
-- * Identifying the client machine/program/module behind a session
-- 

SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET HEADING ON
SET LINESIZE 350
SET PAGESIZE 60
SET TRIMSPOOL ON

COLUMN username FORMAT A22
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 999999
COLUMN status FORMAT A8
COLUMN machine FORMAT A28
COLUMN program FORMAT A28
COLUMN module FORMAT A25
COLUMN action FORMAT A20
COLUMN service_name FORMAT A18
COLUMN sql_id FORMAT A13
COLUMN event FORMAT A35
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN blocker FORMAT 99999
COLUMN final_blocker FORMAT 99999
COLUMN last_call_et FORMAT 99999999
COLUMN logon_time FORMAT A19
COLUMN sql_exec_start FORMAT A19 

SELECT
  LPAD(' ', (LEVEL-1)*2, ' ') ||
  NVL(s.username, '(oracle)') AS username,
  s.osuser,
  s.sid,
  s.serial#,
  s.status,
  s.machine,
  s.program,
  s.module,
  s.action,
  s.service_name,
  s.sql_id,
  TO_CHAR( s.sql_exec_start, 'DD-MON-YYYY HH24:MI:SS' ) AS sql_exec_start,
  s.event,
  s.wait_class,
  s.state,
  s.last_call_et,
  s.blocking_session AS blocker,
  s.final_blocking_session AS final_blocker,
  TO_CHAR( s.logon_time, 'DD-MON-YYYY HH24:MI:SS' ) AS logon_time
FROM
  v$session s
CONNECT BY NOCYCLE PRIOR
  s.sid = s.blocking_session
START WITH
  s.blocking_session IS NULL
ORDER SIBLINGS BY
  s.sid

/