5 Replies Latest reply: Dec 22, 2009 11:08 PM by 742192 RSS

    Materialized View Log

    556287
      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
          fsitja
          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
            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
              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
                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
                  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>