Long Running Queries


Below query can be used to find the Long Running Sessions in the Oracle database and also their % completion. This query can be beneficial for finding RMAN job's aggregate % completion as well.

This is applicable for RAC and Single Instance as well.


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
set lines 500 pages 500
col sid for a5
col message for a90
SELECT inst_id,to_char(sid) sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete",start_time,message
FROM gv$session_longops
WHERE totalwork != 0
AND sofar <> totalwork;



INST_ID SID  SERIAL#  CONTEXT      SOFAR  TOTALWORK % Complete START_TIME          MESSAGE
------- ------------ -------- ---------- ---------- ---------- ------------------- --------------------
      2 5902   60412        1   30812772   58736868      52.46 03-12-2017 20:33:06 RMAN: incremental
      2 5875   40907        0    5744025    6679270         86 03-12-2017 18:38:05 Table Scan: 
      2 5915    8996        4 4020347188 5346034534       75.2 03-12-2017 14:55:47 RMAN: aggregate 
      2 5730   55927        1   56246500   58736868      95.76 03-12-2017 20:18:59 RMAN: incremental
      1 5289      39        0      74623     296585      25.16 28-11-2017 14:13:36 Index Fast Full Scan
      1 1523   65136        0     673298     874963      76.95 03-12-2017 18:46:54 Table Scan:  





Comments