Oracle materialized view application notes

Incremental synchronization of data in two databases

Oracle Database IP Address: 192.168.0.1 (source database), 192.168.0.2 (target database)

1. Create a TEST table in the source database

1. Create table TEST
2. (
3. Id number not null,
4. NAME VARCHAR2 (200)
5. );

create table TEST(  ID          NUMBER not null,  NAME        VARCHAR2(200));

2. Insert a data entry

1. Insert into test (ID, NAME) VALUES (1, '20140901 ');
2. Commit;

INSERT INTO TEST(ID,NAME) VALUES (1,'1111');commit;

3. Create a materialized view log table in the source database

1. Create materialized view log on TEST with rowid;

create materialized view log on TEST with rowid;

4. Create a DBLink link in the target database

1. Create database link DBLINK_TEST
2. Connect TO username identified by "123456"
3. Using '(DESCRIPTION =
4. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))
5. (CONNECT_DATA =
6. (SERVER = DEDICATED)
7. (SERVICE_NAME = orcl)
8. )
9. )';

create database link DBLINK_TESTconnect TO username identified by "123456" using '(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )' ;

5. Create a materialized visual chart MV_TEST for the TEST table in the source database.

1. Create materialized view MV_TEST
2. Refresh fast
3. On demand
4. With rowid
5. As SELECT * from TEST @ DBLINK_TEST;

create materialized view MV_TESTRefresh fast  on demand    with rowid   as SELECT * from TEST@DBLINK_TEST;

When the table is created, the data in the source table is synchronized;

6. manual synchronization

1. Call dbms_mview.refresh ('mv _ test ');