06 Long Running Operations

oracle_dba_perf_toolkit/daily_dba/06_long_running_operations.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 260
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK ON
SET TAB OFF

COLUMN session_id FORMAT A20
COLUMN opname FORMAT A40
COLUMN target FORMAT A45
COLUMN pct_done FORMAT 999.99
COLUMN elapsed_min FORMAT 999,999.99
COLUMN remaining_min FORMAT 999,999.99

SELECT
    sl.inst_id || ':' || sl.sid || ',' || s.serial# session_id,
    sl.opname,
    sl.target,
    ROUND(sl.sofar / NULLIF(sl.totalwork, 0) * 100, 2) pct_done,
    ROUND(sl.elapsed_seconds / 60, 2) elapsed_min,
    ROUND(sl.time_remaining / 60, 2) remaining_min
FROM
    gv$session_longops sl
    JOIN gv$session s
      ON s.inst_id = sl.inst_id
     AND s.sid = sl.sid
WHERE
    sl.totalwork > 0
    AND sl.sofar < sl.totalwork
ORDER BY
    sl.time_remaining DESC;