Oracle materialized view synchronizes data between two databases

Two Oracle 10g databases to synchronize data with materialized views in a specific implementation process

Source Library is 192.168.1.81

Target Library is 192.168.1.96

The server used for the statistics library is 81

Create a read-only user snap_query on the Statistics Gallery 81 for querying

Conn/as SYSDBA

Create user snap_query identified by snap_query

Default Tablespace users

Temporary tablespace temp;

Grant connect to Snap_query;

Grant SELECT on Userb.test1 to Snap_query;

Create a materialized view log for a table that requires these tables to have primary KEY

CREATE materialized VIEW LOG

On Userb.test1

Tablespace TS12

With PRIMARY KEY

Excluding NEW VALUES;

----------------------------

On the test library 96

Log in with the SYS user to view and modify the value of the job_queue_processes

Sql> Show parameter SPFile--check for SPFile first (server-side initialization file)

Sql> Show Parameter Job_queue_process--if 0 is used to modify the command

Sql> alter system set JOB_QUEUE_PROCESSES=10 Scope=spfile;

Create a tablespace for storing materialized view content (i.e. test1)

Create Tablespace Ts_event

DataFile ' i:ts_event_01.dbf ' size 5000m,

' i:ts_event_02.dbf ' size 5000m;

Create a user

Create user Ts12_st identified by ts12_st_2010

Default Tablespace ts_event

Temporary tablespace temp;

Grant Connect,resource,create snapshot to Ts12_st;

Grant CREATE database link to ts12_st;

Configure the network alias in Tnsnames.ora TS12TJ Connect to 81

Creating a database Chain

Conn ts12_st/ts12_st_2010

Create DATABASE link Dblink_snap_query

Connect to Snap_query identified by Snap_query

Using ' TS12TJ ';

Test:

Select Event_id,test1ype

From Userb.test1@dblink_snap_query where rownum<10

Creating materialized views

CREATE materialized VIEW Test1_st2

Build IMMEDIATE

USING INDEX

REFRESH FORCE

NEXT Sysdate + 12/24

As

SELECT

EVENT_ID,

Event_code,

Call_time,

Tele_x,

Tele_y,

Test1ype,

Event_content,

Road_code,

Place,

DIRECTION,

Place_x,

Place_y,

Update_time,

Main_assistant_road

From Userb.test1@dblink_snap_query;

----------------------------

Execute on Statistics Library 81 after creation

Grant SELECT on Mlog$_test1 to Snap_query;

Check for updates on 81:

Select ' Data volume ' | | Count (0) from Test1 Union

Select ' Need to update quantity ' | | Count (0) from Mlog$_test1;

----------------------------

To manually refresh the materialized view methods on 96:

exec dbms_mview.refresh (' Test1_st ');

Company environment Test 3 million time 1 hours

After a long (1-year) run, the log table mlog$_test1 may need move to reclaim space.