Basic Replication -- 4 : Data Dictionary Queries

Now that we have two Materialized Views against a Source table, how can we identify the relationship via the data dictionary ?

This is the query to the data dictionary in the database where the Source Table exists :

SQL> l
  1  select v.owner MV_Owner, v.name MV_Name, v.snapshot_site, v.refresh_method,
  2  l.log_table MV_Log_Name, l.master MV_Source,
  3  to_char(l.current_snapshots,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
  4  from dba_registered_snapshots v, dba_snapshot_logs l
  5  where v.snapshot_id = l.snapshot_id
  6* and l.log_owner = 'HEMANT'
SQL> /

MV_OWNER MV_NAME          SNAPSHOT_SITE      REFRESH_MET MV_LOG_NAME        MV_SOURCE             LAST_REFRESH_DATE
-------- ---------------- ------------------ ----------- ------------------ --------------------- ------------------
HEMANT   MV_OF_SOURCE     ORCLPDB1           PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE          16-SEP-19 22:41:04
HEMANT   MV_2             ORCLPDB1           PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE          16-SEP-19 22:44:37

SQL>

I have run the query on the DBA_REGISTERED_SNAPSHOTS and DBA_SNAPSHOT_LOGS because the join on SNAPSHOT_ID is not available between DBA_REGISTERED_MVIEWS and DBA_MVIEW_LOGS.  Similarly, the CURRENT_SNAPSHOTS column is also not available in DBA_MVIEW_LOGS.  These two columns are important when you have *multiple* MViews against the same Source Table.

Note the "Snapshot_Site" is required because the Materialized View can be in a different database.  In this example, the MViews are in the same database as the Source Table. 

The target database containing the MViews will not have the Source Table "registered" into a data dictionary view.  The Source Table will be apparently from the QUERY column of DBA_MVIEWS (also, if the Source Table is in a different database, look at the MASTER_LINK column to identify the Database Link that connects to the source database).


UPDATE :  In case you are wondering what query you'd write against the database containing the Materialized View(s), you can simply query DBA_MVIEWS.

SQL> l
  1  select mview_name, query, master_link, refresh_mode, refresh_method,
  2  last_refresh_type, to_char(last_refresh_date,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
  3  from dba_mviews
  4  where owner = 'HEMANT'
  5* order by 1 desc
SQL> /

MVIEW_NAME
------------
QUERY
--------------------------------------------------------------------------------
MASTER_LINK  REFRESH_M REFRESH_ LAST_REF LAST_REFRESH_DATE
------------ --------- -------- -------- ---------------------------
MV_OF_SOURCE
SELECT "SOURCE_TABLE"."ID" "ID","SOURCE_TABLE"."DATA_ELEMENT_1" "DATA_ELEMENT_1"
,"SOURCE_TABLE"."DATA_ELEMENT_2" "DATA_ELEMENT_2","SOURCE_TABLE"."DATE_COL" "DAT
E_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE"
             DEMAND    FAST     FAST     16-SEP-19 22:41:04

MV_2
select id, data_element_2
from source_table
             DEMAND    FORCE    FAST     16-SEP-19 22:44:37


SQL>

Here, the MASTER_LINK would specify the name of the Database Link used to connect to the Master (i.e. Source) table, if it was a different database.

REFRESH_MODE is ON DEMAND so that the MVs can be refreshed by either scheduled jobs or manually initiated calls -- as I've done in previous blog posts.  (The alternative can be ON COMMIT, if the Source Table and MV are in the same database).

LAST_REFRESH_TYPE is FAST, meaning that the refresh was able to use the MV Log on the Source Table to identify changes and merge them into the MV.  See the entries from the trace file that I've shown in the previous blog post.

Note the difference in the two REFRESH_METHOD values for the two MVs.
MV_OF_SOURCE was created as "refresh fast on demand" while "MV_2" was created as "refresh on demand".

We'll explore the implications of "REFRESH FAST" and just "REFRESH" alone in a subsequent blog post.

Question : Why does the QUERY look so different between MV_OF_SOURCE and MV_2 ?