The simplest way to create a read-only materialized view site in a replication environment

Create | view | site
Objective: To create a materialized view site that refreshes periodically to obtain changes to the specified table in the main site, which can be used as a query server

Steps:

1. To create a materialized view Log table on the primary site

CREATE materialized VIEW LOG on kamus.account2004;

2. To create a public database link on a materialized view site

Conn System/password

CREATE public DATABASE LINK ORCL using ' ORCL ';

3. To create a refresh group on the materialized view site, in this case, refresh 3 minutes

Conn System/password

BEGIN
Dbms_refresh. Make (
Name => ' Kamus.test_repg ',
List => ',
Next_date => Sysdate,
Interval => ' sysdate + 3/(24*60) ',
Implicit_destroy => FALSE,
Rollback_seg => ',
Push_deferred_rpc => TRUE,
Refresh_after_errors => FALSE);
End;
/

4. To create a user private database link on a materialized view site

Conn Kamus/password

CREATE DATABASE LINK ORCL CONNECT to kamus identified by password;

5. To create a materialized view on a materialized view site

Conn Kamus/password

CREATE materialized VIEW Kamus. ACCOUNT2004 REFRESH FAST with PRIMARY KEY as SELECT * from Kamus. ACCOUNT2004@ORCL;

6. To add a view created on the materialized view site to the refresh group

Conn Kamus/password

EXEC Dbms_refresh. ADD (name => ' Kamus. Test_repg ', List => ' Kamus. ACCOUNT2004 ', lax => TRUE);

7. Test, update the ACCOUNT2004 table in the primary site, check the view in the materialized attempt site for 3 minutes, and discover that the update has replicated successfully.

These are the easiest ways to build a read-only materialized view site:

There is no need to create any other users, such as the propagator needed in the replication environment, the refresher, the receiver, etc.

You do not need to create any replication groups

No replication objects need to be generated and no replication support for any objects is required