Quick Oracle SQL Monitoring Script – Copy‑Paste Ready

This article provides a ready‑to‑run SQL*Plus script that lists currently active sessions in an Oracle database, showing instance ID, session ID, username, elapsed execution time, SQL ID, a snippet of the SQL text, the current event, and wait time in seconds.

Script

col inst_sid heading "INST_ID|:SID" format a7
col username format a1
col machine format a12
col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200

select ses.inst_id||chr(58)||ses.sid as inst_sid,
       username,
       (sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start,
       ses.sql_id,
       substr(sql.sql_text,1,40) sql_text,
       substr(
         (case time_since_last_wait_micro
            when 0 then (case wait_class when 'Idle' then 'IDLE: '||event else event end)
            else 'ON CPU'
          end),1,33) event,
       (case time_since_last_wait_micro
          when 0 then wait_time_micro
          else time_since_last_wait_micro
        end) / 1000000 wait_sec
from gv$session ses, gv$sqlstats sql
where ses.inst_id||chr(58)||ses.sid <> sys_context('USERENV','INSTANCE')||chr(58)||sys_context('USERENV','SID')
  and username is not null
  and status='ACTIVE'
  and ses.sql_id = sql.sql_id (+)
order by sql_exec_start, username, ses.sid, ses.sql_id;

Running the script produces a table similar to the following example:

INST_SID   USERNAME   SQL_EXEC_START        SQL_ID          SQL_TEXT                                   EVENT                                WAIT_SEC
1:1699     YUAN       +00 00:00:00.000000   4nq95bucaf3s1   select sum(l_extendedprice) / 7.0 as avg   IDLE: PX Deq: Table Q Normal         0.014754
1:730      YUAN       +00 00:00:01.000000   04pfkq1nb6tu5   select ps_partkey, sum(ps_supplycost * p   ON CPU                               0.028103
... (additional rows) ...
11 rows selected.

Copy the script into a SQL*Plus session and execute it to instantly view active SQL statements and their wait events, which helps in performance troubleshooting.