lists_active_session_info.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Lists active session information for perticular database user 
-- Call Syntax  : @lists_active_session_info.sql
-- Last Modified: 19/09/2016
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET ECHO ON TRIMSPOOL ON FEEDBACK 1 SQLBL ON TIMING ON LINES 220 PAGES 49999
COLUMN SID FOR 999999
COLUMN serial# 	FORMAT 999999
COLUMN username FORMAT a20
COLUMN osuser 	FORMAT a20
COLUMN machine 	FORMAT a35
COLUMN PROGRAM 	FORMAT a40
COLUMN module 	FORMAT a30 
COLUMN PROCESS 	FORMAT a15 
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
SELECT S.SID,
  S.serial#,
  S.username,
  S.status,
  S.osuser ,
  S.PROCESS,
  S.machine,
  --s.program,
  --S.SQL_ID,
  S.module,
  S.logon_time,
  S.last_call_et
FROM v$session S,
  v$process P
WHERE S.paddr =P.addr 
AND S.username NOT IN ( 'SYS','SYSTEM','DBSNMP','OUTLN','DIP','APPQOSSYS', 'OEM12CCRO','ORACLE_OCM')  
ORDER BY  S.logon_time, S.last_call_et, S.status, S.username, S.osuser;