SELECT SID, serial#, username, (SELECT holding_session FROM dba_waiters WHERE waiting_session = s.SID AND ROWNUM = 1 AND holding_session NOT IN (SELECT waiting_session FROM dba_waiters)) holding_session, DECODE (s.status, 'ACTIVE', ROUND (last_call_et / 60), 0) time_min, DECODE (s.status, 'ACTIVE', last_call_et, 0) time_sec, (SELECT used_urec FROM v$transaction t WHERE t.addr = s.taddr) undo_records, (SELECT ROUND (sl.sofar / sl.totalwork * 100, 2) FROM v$session_longops sl WHERE s.SID = sl.SID AND s.serial# = sl.serial# AND s.status = 'ACTIVE' AND sl.time_remaining > 0) progress, (SELECT event FROM v$session_wait w WHERE w.SID = s.SID) wait_event, (select round(value/1024/1024,2) ||'M' from v$sesstat where sid=s.sid and statistic# = 20) PGA_SIZE, (select round(value/1024/1024,2) ||'M' from v$sesstat where sid=s.sid and statistic# = 15) UGA_SIZE, (select value from v$sesstat where sid=s.sid and statistic# = 4) COMMITS, (SELECT sql_text FROM v$sql t WHERE s.sql_address = t.address AND s.sql_hash_value = t.hash_value AND ROWNUM = 1) sql_used, (SELECT MESSAGE FROM v$session_longops sl WHERE s.SID = sl.SID AND s.serial# = sl.serial# AND s.status = 'ACTIVE' AND sl.time_remaining > 0) long_ops, (SELECT LOWER (ins.instance_name) || '_ora_' || LTRIM (TO_CHAR (a.spid)) || '.trc' filename FROM v$process a WHERE a.addr = s.paddr) trace_file_name, resource_consumer_group, s.status, server, osuser, program, module, action, nvl(lower(s.machine), ins.host_name) Machine FROM v$session s , v$instance ins WHERE username IS NOT NULL AND s.status = 'ACTIVE' AND s.audsid <> USERENV ('sessionid')ORDER BY DECODE (s.status, 'ACTIVE', ROUND (last_call_et), 0) DESC/
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/*SID - Session identifier SERIAL - Session serial numberUSERNAME - Oracle usernameHOLDING_SESSION - Blocker SID (holding a lock on an object for which another session is waiting) TIME_MIN - Active time (minutes)TIME_SEC - Active time (seconds)UNDO_RECORDS - Number of Undo RecordsPROGRESS - progress of work done so far (only for longops operation)WAIT_EVENT - current wait eventPGA_SIZE - PGA sizeUGA_SIZE - UGA sizeCOMMITS - number of commitsSQL_USED - current/last SQL LONG_OPS - Statistics summary message (only for longops operation)TRACE_FILE_NAME - name of the trace filenameRESOURCE_CONSUMER_GROUP - Name of the session's current resource consumer group (Resource Manager feature)STATUS - Status of the sessionSERVER - Server typeOSUSER - Operating system client user namePROGRAM - Operating system program nameMODULE - Name of the currently executing moduleACTION - Name of the currently executing action MACHINE - Operating system machine name*/
