This discussion is archived
5 Replies Latest reply: Dec 22, 2009 9:08 PM by 742192 RSS

Materialized View Log

556287 Newbie
Currently Being Moderated
Hello Every one,I am on oracle 9iR2
I would like to find out, which materialized view is holding data in MLOG$_ table. Every day I observed that the data is not being purged even though the MV;s have been refreshed successfully.
Is there any way we can find out that which MV (refresh ) is holding update.
I hope i am clear in question.
Thank you in advance
  • 1. Re: Materialized View Log
    587383 Expert
    Currently Being Moderated
    The default naming is mlog$_MASTERTABLENAME, below I built an example of a simple fast refresh mview and how to check its log:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> alter session set nls_language = english;
    
    Session altered.
    
    SQL> create table t1 (col1 number primary key);
    
    Table created.
    
    SQL> create materialized view log on t1;
    
    Materialized view log created.
    
    SQL> create materialized view mv_t1
      2    build immediate
      3    refresh fast on commit
      4    as select col1 from t1;
    
    Materialized view created.
    
    SQL> select owner, table_name from all_tables where table_name = 'MLOG$_T1';
    
    OWNER                          TABLE_NAME
    ------------------------------ ------------------------------
    CMZA                           MLOG$_T1
    
    SQL> insert into t1 values (1);
    
    1 row created.
    
    SQL> select * from mlog$_t1;
    
          COL1 SNAPTIME D O CHANGE_VECTOR$$
    ---------- -------- - - --------------------
             1 01/01/00 I N FE
    
    SQL> select * from mv_t1;
    
    no rows selected
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from mv_t1;
    
          COL1
    ----------
             1
    
    SQL> select * from mlog$_t1;
    
    no rows selected
    
    SQL> 
    Edited by: fsitja on Dec 21, 2009 2:21 PM Fixed format

    Edited by: fsitja on Dec 21, 2009 3:33 PM built a more complete example
  • 2. Re: Materialized View Log
    556287 Newbie
    Currently Being Moderated
    Thank you for you feedback, but i know how to check the MLOG@_, but my question is , if i see the data in mLOg$_ table, i want to know which MV refresh is not releasing that data, other words, all mv's are fast refreshed based on that table(LOG) then the log will be purged immediatly but if one of the refresh is not completed and still running the data in log table will be there. So i want to find out which MV is (running ) or holding up data and not letting to purge.
    I hope i am clear now.

    Thank you again.
  • 3. Re: Materialized View Log
    Hoek Guru
    Currently Being Moderated
    So i want to find out which MV is (running ) or holding up data and not letting to purge.
    If I understand correctly:
    You can query V$MVREFRESH to find the MV's currently being refreshed

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_2069.htm#REFRN30157
  • 4. Re: Materialized View Log
    546595 Newbie
    Currently Being Moderated
    Hi,
    Hope this help.
    SELECT an.mview_name NAME,
           an.mview_table_owner owner,
           TO_CHAR (an.last_refresh_date, 'MM-DD-YYYY HH24:MI:SS') last_refresh,
            TO_CHAR (ar.next_date, 'MM-DD-YYYY HH24:MI:SS') next_refresh,,
           ar.INTERVAL,
           an.refresh_method METHOD,
           CASE
             WHEN an.refresh_method = 'FAST' THEN    TRUNC (MOD (an.increfreshtim / 3600, 3600) )
                                                  || ' Hours '
                                                  || TRUNC (MOD (an.increfreshtim / 60, 60) )
                                                  || ' Minutes '
             ELSE TRUNC (MOD (an.fullrefreshtim / 3600, 3600) ) || ' Hours ' || TRUNC (MOD (an.fullrefreshtim / 60, 60) ) || ' Minutes '
           END Refresh_time,
           CASE
             WHEN an.invalid = 'Y' THEN 'Invalid'
             ELSE 'Valid'
           END status,
           SUMMARY GROUP_BY,
           ar.broken ,
           mr.currmvowner owner_crr_refreshing_owner,
           mr.currmvname currently_refreshing_mv
      FROM dba_mview_analysis an,
           v$mvrefresh mr,
           dba_refresh ar
     WHERE an.mview_name = ar.rname(+)
       AND an.mview_name = mr.currmvname(+)
    Edited by: devmiral on Dec 21, 2009 4:32 PM
    Corrected date format
  • 5. Re: Materialized View Log
    742192 Newbie
    Currently Being Moderated
    1) First find the base table on whcih the mv log is created .



    2) Use the below query to find the materialized views depends on the base table .

    select owner, name , type from dba_dependencies where referenced_name = '<base table name>'
    and referenced_owner = '<base table owner name>' and type = 'MATERIALIZED VIEW';

    3) Refresh all the mvs that you get in the above query .

    4) Automatically the data from the log table will be purged .

    For reference please see the below output .

    SQL> create materialized view log on scott.emp;

    Snapshot log created

    SQL> create materialized view onemv as select * from scott.emp;

    Snapshot created

    SQL> create materialized view twomv as select * from scott.emp;

    Snapshot created

    SQL> create materialized view threemv as select * from scott.emp;

    Snapshot created

    SQL> select * from mlog$_emp;

    EMPNO SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
    ----- ----------- --------- --------- --------------------------------------------------------------------------------

    SQL> delete from emp where deptno = 10;

    3 rows deleted

    SQL> commit;

    Commit complete

    SQL> exec dbms_mview.refresh('onemv');

    PL/SQL procedure successfully completed

    SQL> select * from mlog$_emp;

    EMPNO SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
    ----- ----------- --------- --------- --------------------------------------------------------------------------------
    7782 12/23/2009 D O 0000
    7839 12/23/2009 D O 0000
    7934 12/23/2009 D O 0000

    SQL> exec dbms_mview.refresh('twomv');

    PL/SQL procedure successfully completed

    SQL> select * from mlog$_emp;

    EMPNO SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
    ----- ----------- --------- --------- --------------------------------------------------------------------------------
    7782 12/23/2009 D O 0000
    7839 12/23/2009 D O 0000
    7934 12/23/2009 D O 0000

    SQL> exec dbms_mview.refresh('threemv');

    PL/SQL procedure successfully completed

    SQL> select * from mlog$_emp;

    EMPNO SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
    ----- ----------- --------- --------- --------------------------------------------------------------------------------

    SQL>
    SQL> select owner, name , type from dba_dependencies where referenced_name = 'EMP' and referenced_owner = 'SCOTT'
    2 and type = 'MATERIALIZED VIEW';

    OWNER NAME TYPE
    ------------------------------ ------------------------------ -----------------
    SCOTT TWOMV MATERIALIZED VIEW
    SCOTT ONEMV MATERIALIZED VIEW
    SCOTT THREEMV MATERIALIZED VIEW

    SQL>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points