Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: materialized view log getting not empty

    Hello,

    I have a problem with automatic clearing of materialized view logs. These logs will not be cleared after different kinds of refreshs..


    1) update the master table for new rows in the log
    ######################################

    SQL> update mda.mda_mastr set run_time = run_time + 1 where datefrom in ( select max(datefrom) from mda.mda_mastr );

    189 rows updated.

    2) take a look how many rows are placed in the log
    ######################################
    SQL> select count(*) from mda.MLOG$_MDA_MASTR
    2 ;

    COUNT(*)
    ----------
    189 (how expected)

    3) try to refresh the mViw so that the log will be cleared
    ######################################
    SQL> EXECUTE DBMS_MVIEW.REFRESH('MDA.V_PERFORMANCE','C');

    PL/SQL procedure successfully completed.

    SQL> execute DBMS_MVIEW.REFRESH_DEPENDENT(:failures_count, 'MDA.MDA_MASTR', 'C', '', FALSE, FALSE );

    PL/SQL procedure successfully completed.


    3) Is the log empty ???
    ######################################

    SQL> select count(*) from mda.MLOG$_MDA_MASTR;

    COUNT(*)
    ----------
    189

    I have made a complete refresh to all dependant mViews and the log is still filled.. Why ? please help

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What version of Oracle ... I have seen where the data just hangs around in the log ... even after the refresh and purge ...

    Find out what you have ...

    SELECT TRUNC(SNAPTIME$$),COUNT(*)
    FROM MLOG$_yourtablename
    GROUP BY TRUNC(SNAPTIME$$);

    Remove the old data - 2 keeps the last 2 refreshes .
    begin
    dbms_snapshot.purge_log('yourtablename',2,'DELETE' );
    END;
    /


    HTH
    Gregg

  3. #3
    Join Date
    Aug 2004
    Posts
    9

    my Oracle Version .. snapshot Times, one more test

    ########### ORACLE VERSION OF CLIENT == SERVER

    SQL*Plus: Release 9.0.1.0.1 - Production on Fri Nov 26 15:05:45 2004

    (c) Copyright 2001 Oracle Corporation. All rights reserved.

    Enter user-name: mda/mda@oraw2k92

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    ########### CURRENT MLOG COUNT ###########

    SQL> select count(*) from mda.mlog$_mda_mastr;

    COUNT(*)
    ----------
    423703

    ########### MVIEW REFRESH

    SQL> variable nr number;
    SQL> execute DBMS_MVIEW.REFRESH_DEPENDENT(:nr, 'MDA.MDA_MASTR', 'C', '', FALSE, FALSE );

    PL/SQL procedure successfully completed.

    SQL> select count(*) from mda.mlog$_mda_mastr;

    COUNT(*)
    ----------
    423703

    ########### SNAPTIMES ?? IS HERE THE CAUSE ?

    SQL> SELECT TRUNC(SNAPTIME$$),COUNT(*) FROM MLOG$_mda_mastr GROUP BY TRUNC(SNAPTIME$$);

    TRUNC(SNA COUNT(*)
    --------- ----------
    01-JAN-00 423703

    SQL>

    ########### non-automated delete of mView Log

    SQL> execute dbms_snapshot.purge_log('mda.mda_mastr',2,'DELETE' );

    PL/SQL procedure successfully completed.

    SQL> select count(*) from mda.mlog$_mda_mastr;

    COUNT(*)
    ----------
    0


    ########

    ->> IS The Oracle version the cause of the missed automatic purge of the mView Log Table ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •