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
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...
 |