OracleSessions

SQL active for 30 minutes or more (all instances)

Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and full SQL text. SYS, SYSTEM and grid sessions are excluded.

All RAC instances

Tested on 12c, 19c · verified Sep 2026

ora-long-running-sql.sql

1CLEAR COLUMNS2set linesize 200 pages 1000 termout on feedback off long 20003column INSTANCE_NAME format a84column executions format 99999999995column LastCallSec format 99999999996column Program  format a157column SID format 999998column serial# format 99999999column SPID format a1010column OSUSER format a811column Client_info format a2012column sql_id format a1513column plan_hash_value format 999999999914column SQL_TXT format a8515 16select i.instance_name,executions,s.last_call_et LastCallSec,s.program, s.sid,s.serial#,17 p.spid, s.osuser,  s.client_info,s.module,18b.sql_id,b.plan_hash_value,b.sql_fulltext SQL_TXT19 from gv$session s, gv$process p, gv$sql b, gv$instance i20 where s.sql_id=b.sql_id21 and s.paddr = p.addr22 and s.osuser is not null23 and user#<>024 and s.status='ACTIVE'25 and last_Call_et >= 180026 and s.username not in ('SYS','SYSTEM')27 and s.osuser not in ('grid')28and s.inst_id=p.inst_id29and p.inst_id=b.inst_id30and s.inst_id=i.inst_id31order by 3;32 33set feedback on

Save it as ora-long-running-sql.sql and run it with SQL> @ora-long-running-sql.

Open in denrepo

Helps with

ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small

More Oracle scripts: Sessions

Long operations with time remaining (all instances)Long operations still running on any instance, with percent done and elapsed, remaining and estimated total minutes. RMAN is left out because it has…
Active user sessionsUser sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they…
Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
Everything about one sessionPrompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.
Find the session behind an OS process IDWhen top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.
Long-running operations in progressFull scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.