myorama
7/31/2014 - 10:19 AM

estimate remaining time.sql

SET LINESIZE 200
SET PAGESIZE 200
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;
set lines 250
set pages 1000
SELECT SID, SERIAL#, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) AS "%_COMPLETE",
       elapsed_seconds,
       time_remaining AS time_remaining_seconds,
       to_char(start_time, 'YYYYMMDD HH24:MI') AS start_time,
       to_char(last_update_time + time_remaining/86400, 'YYYYMMDD HH24:MI') AS estim_end,
       to_char(trunc(sysdate) + time_remaining/86400, 'HH24:MI') AS time_remaining_fmt
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
;