Oracle materialized view synchronize data between two databases

Two oracle10g databases, specific implementation process of Data Synchronization Through materialized views the source database is 192.168.1.81 the target database is 192.168.1.96 the server used by the statistics database is 81 the read-only user snap_query is created on the Statistics Database 81, used to query connassysdbacreateusersnap_queryidentifiedbysnap_querydefaultt

Two oracle 10 Gb databases, specific implementation process of Data Synchronization Through materialized views the source database is 192.168.1.81 the target database is 192.168.1.96 the server used for the statistics database is 81 the read-only user snap_query is created on the Statistics Database 81, used to query conn/as sysdba create user snap_query identified by snap_query default t

Two oracle 10 Gb databases synchronize data through materialized views

The source database is 192.168.1.81.

The target database is 192.168.1.96.

The server used for the statistics library is 81.

Create a read-only user snap_query on the statistical database 81 for query

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 the table, requiring that these watchband have a PRIMARY KEY

CREATE MATERIALIZED VIEW LOG

ON userb. test1

TABLESPACE ts12

WITH PRIMARY KEY

Excluding new values;

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

On Test Database 96

Log On As A sys user, view and modify the job_queue_processes Value

SQL> show parameter spfile -- first check whether it is a spfile (server-side initialization file)

SQL> show parameter job_queue_process -- if it is zero, use the following command to modify

SQL> alter system set job_queue_processes = 10 scope = spfile;

Create a tablespace to store the Materialized View content (test1)

Create tablespace ts_event

Datafile 'I: ts_event_01.dbf' size 5000 m,

'I: ts_event_02.dbf' size 5000 m;

Create 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 ts12tj in tnsnames. ora to connect to 81

Create 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

Create Materialized View

Create materialized view test1_st2

BUILD IMMEDIATE

USING INDEX

REFRESH FORCE

NEXT sysdate more than 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;

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

Run the command on the statistical database 81 after the creation.

Grant select on mlog $ _ test1 to snap_query;

Check the Update Status on 81:

Select 'data size' | count (0) from test1 union

Select 'Update required '| count (0) from mlog $ _ test1;

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

On the 96, manually refresh the Materialized View:

Exec dbms_mview.refresh ('test1 _ st ');

It takes 1 hour to test 3 million articles in the company's Environment

After a long (1 year) operation, the mlog $ _ test1 log table may need to be moved to recycle space.