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;
