How to check long-running operations in Oracle

V$SESSION_LONGOPS tracks any operation Oracle expects to run longer than six seconds — think RMAN backups, big DML, index rebuilds, statistics gather. Two views below: a compact SID / serial / progress table, and an enriched version with username, start time, and the OPNAME message.

-- Compact view: SID, elapsed, remaining, progress
COLUMN sid          FORMAT 999
COLUMN serial#      FORMAT 9999999
COLUMN machine      FORMAT a30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed      FORMAT a10
COLUMN remaining    FORMAT a10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds / 60) || ':' || MOD(sl.elapsed_seconds, 60) AS elapsed,
       ROUND(sl.time_remaining  / 60) || ':' || MOD(sl.time_remaining,  60) AS remaining,
       ROUND(sl.sofar / sl.totalwork * 100, 2) AS progress_pct
FROM   v$session         s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;


-- Enriched view: with username, start / last-update timestamps, and the OPNAME message
ALTER SESSION SET nls_date_format = 'DD-MON-YY HH24:MI:SS';
SET LINES 200
COL sid       FORMAT 9999
COL serial#   FORMAT 9999999
COL username  FORMAT a10
COL remaining FORMAT a9
COL message   FORMAT a80

SELECT sid, serial#, username,
       start_time,
       last_update_time,
       TO_CHAR(TRUNC(SYSDATE) + (time_remaining  / 24 / 60 / 60), 'HH24:MI:SS') AS remaining,
       TO_CHAR(TRUNC(SYSDATE) + (elapsed_seconds / 24 / 60 / 60), 'HH24:MI:SS') AS elapsed,
       ROUND(sofar / totalwork, 2) * 100 AS pct_progress,
       message
FROM   v$session_longops
WHERE  totalwork > sofar;