This discussion is archived
0 Replies Latest reply: Oct 2, 2012 6:39 AM by user7955891 RSS

No entry in slog$

user7955891 Newbie
Currently Being Moderated
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

Legend

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