News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Monitor long running operations using v$session_longops

  • Posted by Gavin Soorma
  • On June 30, 2009
  • 0 Comments
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

Note: the same query can be used to monitor RMAN backup status

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR  != TOTALWORK
;

       SID    SERIAL# OPNAME                    SOFAR  TOTALWORK   COMPLETE
---------- ---------- -------------------- ---------- ---------- ----------
       604      13371 Table Scan                 6311      24498      25.76
       685       1586 Table Scan                 6333      24498      25.85
 3

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *