Managing Long operations

select * from V$SESSION_LONGOPS;

select * from (

  select opname, target, sofar, totalwork,

         units, elapsed_seconds, message

  from v$session_longops

  ==sid||serial# in (39149)

  order by start_time desc)

where rownum <=1;

Version 1:

-----

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;

  

  If no progress:

  SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait

          WHERE wait_time = 0

          ORDER BY sid;

  

SQL> SELECT sid, serial#, opname, time_remaining

 FROM v$session_longops 

 Where sid||serial# in (39149)

 AND time_remaining > 0;  

 

Version 2:

-----

SELECT TO_CHAR(start_time,'DD-MON-YY HH24:MI') "Backup Started At",

DECODE(TRUNC(time_remaining/86400), 0, TO_CHAR(TO_DATE(time_remaining, 'SSSSS'), 'HH24:MI:SS'), 

TRUNC(time_remaining/86400) || ' Days + '

|| TO_CHAR(TO_DATE(time_remaining - (TRUNC(time_remaining/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) REMAINING,

DECODE(TRUNC(ELAPSED_SECONDS/86400), 0, TO_CHAR(TO_DATE(ELAPSED_SECONDS, 'SSSSS'), 'HH24:MI:SS'), 

TRUNC(ELAPSED_SECONDS/86400) || ' Days + '

|| TO_CHAR(TO_DATE(ELAPSED_SECONDS - (TRUNC(ELAPSED_SECONDS/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) 

ELAPSED,

elapsed_seconds/60 "ELAPSED_MIN",round(time_remaining/60) min_remaining,ROUND(SOFAR/TOTALWORK*100,2) "COMPLETE_%"

fROM v$session_longops

WHERE opname like 'RMAN%'

--WHERE opname like '%full datafile restore%'        --RMAN datafiles restore

and opname like '%RMAN: archived log restore%'        --RMAN Archivelog restore/recover

and sofar <> totalwork

and TOTALWORK != 0

select * from V$RECOVERY_PROGRESS;