OracleSessions

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 its own progress script, and so are aggregate rows and operations Oracle can't estimate yet.

All RAC instances

Not yet verified. How scripts are tested

ora-longops-rac.sql

1CLEAR COLUMNS2SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN inst        HEADING "INST" FORMAT 9904COLUMN sid_serial  HEADING "SID,SERIAL#" FORMAT A145COLUMN username    FORMAT A206COLUMN sql_id      FORMAT A137COLUMN opname      FORMAT A25 TRUNCATE8COLUMN target      FORMAT A30 TRUNCATE9COLUMN started     FORMAT A1410COLUMN pct_done    HEADING "% DONE"      FORMAT 990.9911COLUMN elapsed_min HEADING "ELAPSED MIN" FORMAT 99,990.912COLUMN remain_min  HEADING "REMAIN MIN"  FORMAT 99,990.913COLUMN total_min   HEADING "TOTAL MIN"   FORMAT 99,990.914COLUMN message     FORMAT A60 WORD_WRAPPED15 16SELECT inst_id AS inst,17       sid || ',' || serial# AS sid_serial,18       username, sql_id, opname, target,19       TO_CHAR(start_time, 'MM-DD HH24:MI:SS') AS started,20       ROUND(sofar / totalwork * 100, 2) AS pct_done,21       ROUND(elapsed_seconds / 60, 1) AS elapsed_min,22       ROUND(time_remaining / 60, 1) AS remain_min,23       ROUND((elapsed_seconds + time_remaining) / 60, 1) AS total_min,24       message25FROM gv$session_longops26WHERE opname NOT LIKE 'RMAN%'27  AND opname NOT LIKE '%aggregate%'28  AND totalwork <> 029  AND sofar <> totalwork30  AND time_remaining > 031ORDER BY inst_id, start_time;

CLEAR COLUMNS
SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN inst        HEADING "INST" FORMAT 990
COLUMN sid_serial  HEADING "SID,SERIAL#" FORMAT A14
COLUMN username    FORMAT A20
COLUMN sql_id      FORMAT A13
COLUMN opname      FORMAT A25 TRUNCATE
COLUMN target      FORMAT A30 TRUNCATE
COLUMN started     FORMAT A14
COLUMN pct_done    HEADING "% DONE"      FORMAT 990.99
COLUMN elapsed_min HEADING "ELAPSED MIN" FORMAT 99,990.9
COLUMN remain_min  HEADING "REMAIN MIN"  FORMAT 99,990.9
COLUMN total_min   HEADING "TOTAL MIN"   FORMAT 99,990.9
COLUMN message     FORMAT A60 WORD_WRAPPED

SELECT inst_id AS inst,
       sid || ',' || serial# AS sid_serial,
       username, sql_id, opname, target,
       TO_CHAR(start_time, 'MM-DD HH24:MI:SS') AS started,
       ROUND(sofar / totalwork * 100, 2) AS pct_done,
       ROUND(elapsed_seconds / 60, 1) AS elapsed_min,
       ROUND(time_remaining / 60, 1) AS remain_min,
       ROUND((elapsed_seconds + time_remaining) / 60, 1) AS total_min,
       message
FROM gv$session_longops
WHERE opname NOT LIKE 'RMAN%'
  AND opname NOT LIKE '%aggregate%'
  AND totalwork <> 0
  AND sofar <> totalwork
  AND time_remaining > 0
ORDER BY inst_id, start_time;

Save it as ora-longops-rac.sql and run it with SQL> @ora-longops-rac.

Open in denrepo

More Oracle scripts: Sessions

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…
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.