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.name, 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’
ORDER BY SNAPTIME DESC;

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

EXEC DBMS_MVIEW.REGISTER_MVIEW (‘Mview_SCHEMA’,‘MVTABLE’,‘DB1’,525,33,‘SELECT * from table’,DBMS_MVIEW.REG_UNKNOWN );

The variables the Register_Mview procedure is based on:

DBMS_MVIEW.REGISTER_MVIEW (
   mviewowner   IN   VARCHAR2,
   mviewname    IN   VARCHAR2,
   mviewsite    IN   VARCHAR2,
   mview_id     IN   DATE | BINARY_INTEGER,
   flag         IN   BINARY_INTEGER,
   qry_txt      IN   VARCHAR2,
   rep_type     IN   BINARY_INTEGER := DBMS_MVIEW.REG_UNKNOWN);

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’,
       ‘UNKNOWN’))))) REFRESH_METHOD
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

 

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: