-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 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;