Re: Materialized View Progress

From: sim <jmenker_at_muenster.de>
Date: 15 Nov 2005 00:30:26 -0800
Message-ID: <1132043426.378167.128330@g47g2000cwa.googlegroups.com>

You could use the following SQL statement to observe long ops:

SELECT A.SID,

       A.OPNAME OPERATION,
       NVL(B.ACTION, 'not available') ACTION,
       CASE
          WHEN A.TARGET IS NOT NULL
             THEN A.TARGET
          ELSE NVL(A.TARGET_DESC, ' unknown ')
       END OBJECT,
       B.STATUS,
       B.STATE WAIT_ACTIVITY,
       B.WAIT_CLASS W_CLAS,
       TO_CHAR(A.SOFAR) || ' / ' || TO_CHAR(A.TOTALWORK) || ' (' ||
SUBSTR(A.UNITS, 1, 1) || ')' PROGRESS,
       LPAD(TO_CHAR(FLOOR(A.ELAPSED_SECONDS / 3600)), 2, '0') || ':' ||

LPAD(TO_CHAR(FLOOR(MOD(A.ELAPSED_SECONDS,3600) / 60)), 2, '0') || ':' || LPAD(TO_CHAR(MOD(A.ELAPSED_SECONDS, 60)), 2, '0') SOFAR,        LPAD(TO_CHAR(FLOOR(A.TIME_REMAINING / 3600)), 2, '0') || ':' || LPAD(TO_CHAR(FLOOR(MOD(A.TIME_REMAINING,3600) / 60)), 2, '0') || ':' || LPAD(TO_CHAR(MOD(A.TIME_REMAINING, 60)), 2, '0') REMAINING,        LPAD(TO_CHAR(FLOOR((A.TIME_REMAINING + A.ELAPSED_SECONDS) / 3600)), 2, '0') || ':' || LPAD(TO_CHAR(FLOOR(MOD((A.TIME_REMAINING + A.ELAPSED_SECONDS),3600) / 60)), 2, '0') || ':' || LPAD(TO_CHAR(MOD((A.TIME_REMAINING + A.ELAPSED_SECONDS), 60)), 2, '0') TOTAL,
       ' ' || TO_CHAR(ROUND(A.SOFAR / A.TOTALWORK * 100, 0)) || ' %' PROZ
FROM V$SESSION_LONGOPS A,
       V$SESSION B
WHERE A.SID = B.SID
AND A.TIME_REMAINING > 0 sim Received on Tue Nov 15 2005 - 02:30:26 CST