longs.sql

SET VERIFY OFF FEED OFF LINES 185

COL "USER" FORMAT A40
COL OPNAME FORMAT A40 HEAD "OPERACAO"
COL INICIO FORMAT A11
COL RESTANTE FORMAT A11
COL PASSADO FORMAT A11
COL PERCENTUAL FORMAT A7
COL OBJETO FORMAT A50
BREAK ON USER SKIP PAGE
CLEAR BREAK
SELECT
   RPAD( L.USERNAME || ' '''||L.SID||','||L.SERIAL#||',@'||L.INST_ID||'''', 40, ' ' ) "USER"
  ,UPPER(L.OPNAME) OPNAME
  ,TO_CHAR(L.START_TIME, 'DD/MM HH24:MI' ) INICIO
  ,TO_CHAR(TRUNC(L.TIME_REMAINING/3600),'fm9900')||':'||TO_CHAR(TO_DATE('1','J')+(MOD(L.TIME_REMAINING,3600)/86400),'MI:SS') RESTANTE
  ,TO_CHAR(TRUNC(L.ELAPSED_SECONDS/3600),'fm9900')||':'||TO_CHAR(TO_DATE('1','J')+(MOD(L.ELAPSED_SECONDS,3600)/86400),'MI:SS') PASSADO
  ,TO_CHAR(L.SOFAR*100/DECODE(L.TOTALWORK, 0, 1, L.TOTALWORK), '990D00' ) PERCENTUAL
  ,SUBSTR( nvl(L.TARGET, 'SqlId='|| s.sql_id), 1, 40 ) OBJETO
FROM GV$SESSION_LONGOPS L
JOIN GV$SESSION S ON S.SID = L.SID AND S.SERIAL# = L.SERIAL#
WHERE L.SOFAR < L.TOTALWORK
AND  L.USERNAME LIKE UPPER( '&1.' )
ORDER BY L.USERNAME, OBJETO, PERCENTUAL DESC, L.START_TIME
/
PROMPT
SET FEED 6 VERIFY ON