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.STATUS='ACTIVE'
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;