Materialized View Replication

The use of materialized views, or snapshots as they were previously known, is the simplist way to achive replication of data between sites. The materialized view is a table whose contents are periodically refreshed using a query against a remote table. The example code below assumes that TSH1 is the master instance and TSH2 is the materialized view site.

Check Privileges
Create Local Objects
Create Remote Objects
Cleaning Up

Check Privileges

First we must check that the user who will own the materialized views has the correct privileges:

CONNECT sys@tsh2

GRANT CREATE DATABASE LINK TO scott;
GRANT CREATE MATERIALIZED VIEW TO scott;

Create Local Objects

Next we connect to the materialized view owner and create the database link and the materialized view itself:

CONNECT scott/tiger@tsh2

CREATE DATABASE LINK TSH1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'TSH1.WORLD';

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@tsh1.world;

BEGIN
  Dbms_Stats.Gather_Table_Stats(
    ownname => 'SCOTT',
    tabname => 'EMP_MV');
END;
/

The FORCE parameter means the materialized view will attempt a FAST refresh. If this cannot be performed a COMPLETE refresh will occur.

We then create a refresh group which is defined to refresh every minute and assign our materialized view to it:

BEGIN
   Dbms_Refresh.Make(
     name => 'SCOTT.MINUTE_REFRESH',
     list => '',
     next_date => SYSDATE,
     interval => '/*1:Mins*/ sysdate + 1/(60*24)',
     implicit_destroy => FALSE,
     lax => FALSE,
     job => 0,
     rollback_seg => NULL,
     push_deferred_rpc => TRUE,
     refresh_after_errors => TRUE,
     purge_option => NULL,
     parallelism => NULL,
     heap_size => NULL);
END;
/

BEGIN
   Dbms_Refresh.Add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax => TRUE);
END;
/

At this point the materialized view is created and will refresh every minute.

Create Remote Objects

Since the refresh involves truncating the materialized view table and repopulating it completely, it can be quite time consuming an involve a considerable amount of network traffic. To reduce the replication costs all changes to the base table since the last refresh can be stored in materialized view logs. With this data a FAST refresh only needs to apply the changes rather than a COMPLETE refresh of the materialized view.

To take advantage of the of the FAST refresh we must connect to the master instance and create the materialized view log:

CONNECT scott/tiger@tsh1

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Cleaning Up

To clean up we must remove all objects:

CONNECT scott/tiger@tsh2
DROP MATERIALIZED VIEW emp_mv;
DROP DATABASE LINK TSH1.WORLD;

BEGIN
   Dbms_Refresh.Destroy(name => 'SCOTT.MINUTE_REFRESH');
END;
/

CONNECT scott/tiger@tsh1
DROP MATERIALIZED VIEW LOG ON scott.emp;

Hope this helps. Regards Tim...

Back to the Top.