Find Site holding up mlog & Register Orphaned Mviews

To find which site is holding up an Mlog purge, run the following on the MASTER site, changing criteria where necessary:

select sl.*, rs.snapshot_site, rs.owner,, rs.refresh_method from sys.slog$ sl
left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID
where mowner = ‘schema_owner’
and Master = ‘table’

To Register an orphaned Mview destination at the master, run the following, changing where relevant:


The variables the Register_Mview procedure is based on:

   mviewowner   IN   VARCHAR2,
   mviewname    IN   VARCHAR2,
   mviewsite    IN   VARCHAR2,
   mview_id     IN   DATE | BINARY_INTEGER,
   flag         IN   BINARY_INTEGER,
   qry_txt      IN   VARCHAR2,

The Flag variable above is based on a combination

The possible values for the flag parameter are:
                   1 – Fast refreshable (Can_use_log)
                   2 – updatable mview
                  16 – rowid mview
                  32 – primary key mview
           536870912 – object id mview

A materialized view can have more than one of these properties. In this case, sum the values to specify more than one property. (Use the query below to get the values for the properties)

To get the information needed to fill out the variables for the Register_Mview procedure, run the following at the Mview Destination site, changing criteria where necessary:

select s.sowner OWNER, s.vname NAME, snapid,
decode(bitand(s.flag,1),  0, ‘NO’, ‘YES’) CAN_USE_LOG,
       decode(bitand(s.flag,2),  0, ‘NO’, ‘YES’) UPDATABLE,
       decode(bitand(s.flag,16),  16, ‘ROWID’,
       (decode(bitand(s.flag,32), 32, ‘PRIMARY KEY’,
       (decode(bitand(s.flag,536870912),   536870912, ‘OBJECT ID’,
from sys.snap$ s
where sowner = ‘Mview_SCHEMA’;

OWNER             NAME                    SNAPID      CAN_USE_LOG UPDATABLE   REFRESH_METHOD

Mview_SCHEMA     MVTABLE                525         YES         NO          PRIMARY KEY

The flag for the record above would be Can_Use_Log (1) + refresh_Method (32) = 33



2 Responses to Find Site holding up mlog & Register Orphaned Mviews

  1. Pingback: Unregister Mview Snapshot Cleanly « Mohsin's DBA Blog

  2. Iona says:

    U put together a lot of outstanding tips inside ur article,
    “Find Site holding up mlog & Register Orphaned Mviews Mohsin’s DBA Blog”. I will remain coming to ur blog in the near future. Thanks ,Bridget

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: