0 Replies Latest reply: Oct 2, 2012 8:39 AM by user7955891 RSS

    No entry in slog$

    user7955891
      Master site: Oracle db ee 11.2.0.1.0 on Windows Server 2008R2 Std.
      Rep Site: Oracle db ee 11.1.0.7.0 on Solaris.

      On master site, I have a table mv_test with materialized view log created.
      select master, oldest_pk, youngest from sys.mlog$ where master = 'MV_TEST';
      MASTER OLDEST_PK YOUNGEST
      ----------------------------------------------------
      MV_TEST 01-OCT-12 01-OCT-12

      If I create MV, testmv, locally and query sys.slog$ table:
      select * from sys.slog$ where master = 'MV_TEST';
      MOWNER MASTER SNAPSHOT SNAPID SSCN SNAPTIME TSCN USER#
      ------------------------------------------
      USER_NAME MV_TEST 321 02-OCT-12 7956962134

      and the dba_registered_mviews where name = 'TESTMV';
      select NAME, MVIEW_SITE, MVIEW_ID from dba_registered_mviews where name = 'TESTMV';
      NAME MVIEW_SITE MVIEW_ID
      ----------------------------------------
      TESTMV MASTER_SITE 321

      All good. However, when I create the same MV (after dropping the local one) from rep site, it is different.
      create materialized view testmv refresh force as select * from mv_test@master_site;
      Materialized view created.

      Again,
      select master, oldest_pk, youngest from sys.mlog$ where master = 'MV_TEST';
      MASTER OLDEST_PK YOUNGEST
      -----------
      MV_TEST 02-OCT-12 02-OCT-12
      select * from sys.slog$ where master = 'MV_TEST';
      no rows selected
      select NAME, MVIEW_SITE, MVIEW_ID from dba_registered_mviews where name = 'TESTMV';
      NAME MVIEW_SITE MVIEW_ID
      -----------------------------
      TESTMV REP_SITE 322

      The remote MV was created, registered with master site, and can be fast refreshed. However, since no entry in slog$, the MV log at master site is not purged after fast refresh.

      Please help me understand this. Is this a bug? Can anything be done so that the log is purged after refresh?

      Edited by: user7955891 on Oct 2, 2012 6:37 AM

      Edited by: user7955891 on Oct 2, 2012 6:37 AM

      Edited by: user7955891 on Oct 2, 2012 6:38 AM

      Edited by: user7955891 on Oct 2, 2012 6:38 AM