Hi,
I am having little confusion in how to proceed ahead with the purging of materialized view log.The database version is 10.2.0.3 and hosted on AIX box.
The materialized view log on the master site is almost 10Gb.
16:09:40 SQL> select LOG_OWNER,MASTER,LOG_TABLE,LOG_TRIGGER,PRIMARY_KEY from dba_mview_logs where log_owner='TEST' and MASTER='CON_DET';
LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER PRI
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
TEST CON_DET MLOG$_CON_DET YES
16:10:17 SQL> select segment_name,segment_type,(bytes/1024/1024)MB from dba_segments where segment_name='MLOG$_CON_DET';
SEGMENT_NAME SEGMENT_TYPE MB
---------------------------------------- ------------------ ----------
MLOG$_CON_DET TABLE 9804
This "MLOG$_CON_DET" log is what i want to purge.The materialized view refresh is confirmed to be a fast refresh.
Interestingly,
11:35:03 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='TEST' and master='CON_DET';
MOWNER MASTER SNAPSHOT SNAPID SNAPTIME SSCN USER#
------------------------------ ------------------------------ --------- ---------- --------- ---------- ----------
TEST CON_DET 442 13-JUN-08
TEST CON_DET 500 22-JUL-10
sys.slog$ shows 2 snapid for CON_DET master table.I think this might be the reason for the log not getting purged.Checking for registered mview log
15:51:21 SQL> SELECT r.NAME snapname, snapid,
15:51:54 2 NVL(r.snapshot_site, 'not registered') snapsite, snaptime
15:51:54 3 FROM sys.slog$ s, dba_registered_snapshots r
15:51:54 4 WHERE s.snapid=r.snapshot_id(+) AND
15:51:54 5 mowner LIKE UPPER('&owner')
15:51:54 6 AND MASTER LIKE UPPER('&table_name');
Enter value for owner: test
old 5: mowner LIKE UPPER('&owner')
new 5: mowner LIKE UPPER('orion')
Enter value for table_name: CON_DET
old 6: AND MASTER LIKE UPPER('&table_name')
new 6: AND MASTER LIKE UPPER('CON_DET')
SNAPNAME SNAPID SNAPSITE SNAPTIME
------------------------------ ------ ------------------------------ ------------------------------
442 not registered 13-06-08 00:16:32
CON_DET 500 ORTL 22-07-10 00:52:24
Elapsed: 00:00:00.03
14:49:24 SQL> select min(SNAPTIME$$) from test.mlog$_CON_DET;
MIN(SNAPTIME$$)
-----------------
13-06-08 14:45:34
What would be the best way to purge mview log? What all i can think of is :-
1. Using execute dbms_snapshot.purge_log('TEST.CON_DET',1); and then ALTER MATERIALIZED VIEW LOG '&snapshot_log' SHRINK SPACE.
Will dbms_snapshot.purge_log help in this case?I am little confused over it, as snapid 442 shows not registered.As per metalink
dbms_snapshot.purge_log will remove entries from the snapshot log (MLOG$_TABLENAME) as well as remove the entry of that snapshot (de-registering the snapshot) from the sys.slog$ table.In my case 442 snapid already shows de-registered.
2. Truncating the materialized view log as mentioned here [http://download.oracle.com/docs/cd/B19306_01/server.102/b14227/rarmanmv.htm#sthref702] . Will using this option won't cause increase in mview log size in future.
3. What will be the effect of dropping of materialized view and re-creating it.Will the snapid 442 be removed from sys.slog$.
Regards
Anand