Mview refresh progress
                                                 Last update (2008-02-29 10:39:02)
                                                                                                                   Date added (2008-02-29 10:30:29)

Summary
You can find the refresh progress of a Mview with this script
SELECT  CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR 
               "MVIEW BEING REFRESHED",
           DECODE( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
           DECODE(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 
                                      3, 'WRAPUP', 'UNKNOWN' ) STATE,
           TOTAL_INSERTS_KNSTMVR INSERTS,
           TOTAL_UPDATES_KNSTMVR UPDATES,
           TOTAL_DELETES_KNSTMVR DELETES
   FROM X$KNSTMVR X
   WHERE type_knst=6 AND 
         EXISTS (SELECT 1 FROM v$session s 
                 WHERE s.SID=x.sid_knst AND 
                       s.serial#=x.serial_knst);

SELECT l.SID, 
          DECODE( COUNT(*), 0, 'No purge in progress', 
                               'Purge is in progress' ) State
   FROM v$lock l, DBMS_LOCK_ALLOCATED la
   WHERE l.TYPE='UL' AND 
         l.lmode=6 AND
         l.id1=la.lockid AND
         la.NAME='ORA$DEF$EXE$PurgeCommonLock'
   GROUP BY l.SID;

SELECT l.SID, 
          DECODE( COUNT(*), 0, 'No propagation in progress', 
                               'Propagation in progress' ) State
   FROM v$lock l, DBMS_LOCK_ALLOCATED la
   WHERE l.TYPE='UL' AND 
         l.lmode=4 AND
         l.id1=la.lockid AND
         la.NAME='ORA$DEF$EXE$PushCommonLock'
   GROUP BY l.SID;

SELECT /*+ RULE */ o.owner, o.object_name mview, username, s.SID 
   FROM v$lock l, dba_objects o, v$session s 
   WHERE o.object_id=l.id1 AND 
         l.TYPE='JI' AND 
         l.lmode=6   AND 
         s.SID=l.SID AND 
         o.object_type='TABLE'; 
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search