Saturday 23 March 2013

V$SESSION_LONGOPS Time Remaining

V$SESSION_LONGOPS with Time Remaining in day, hour, minute, and second format:

col ETA form a20
col TARGET form a30
col MESSAGE form a70
col USERNAME form a12
SELECT *
FROM (SELECT INST_ID
     ,SID
     ,SERIAL#
     ,USERNAME
     ,OPNAME
     ,DECODE(TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ',NULL,TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ','0h ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'s ','0m ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'m ')||
     TO_CHAR(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR), 60)) ||'s' ETA
     ,MESSAGE
     FROM GV$SESSION_LONGOPS
     WHERE TOTALWORK<>SOFAR
     AND SOFAR<>0
     ORDER BY START_TIME DESC);

    

No comments:

Post a Comment