Saturday, May 24, 2014



Script to list out long running operations or sql executions in Oracle...


SELECT s.sid,
       s.serial#,
       S.username,
       s.OSUser,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK
order by 1;

No comments: