Oracle Session Monitoring Scripts

Below are the oracle session monitoring scripts for the database version 9i, 10g and above versions.

  • For Database Version 9i:
SELECT DISTINCT last_call_et, s.username, s.SID, s.module, hash_value,
                w.event, row_wait_obj#, sql_text
           FROM v$sql q, v$session s, v$session_wait w
          WHERE s.sql_hash_value = q.hash_value(+)
            AND s.SID = w.SID
            AND s.status = 'ACTIVE'
            AND s.username IS NOT NULL
       ORDER BY 1 DESC;
  • For Database Version 10g and above:
SELECT DISTINCT last_call_et, s.username, s.SID, s.program, s.sql_id, event,
                sql_text
           FROM v$session s, v$sql q
          WHERE s.sql_hash_value = q.hash_value(+)
            AND s.status = 'ACTIVE'
            AND s.username IS NOT NULL
       ORDER BY 1 DESC;
  • Session Long-ops Query:
select username,sid,message,time_remaining,elapsed_seconds from v$session_longops
where time_remaining>0
Oracle Session Monitoring Scripts

Leave a Reply