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
- Create table TEST
- (
- Id number not null,
- NAME VARCHAR2 (200)
- );
create table TEST( ID NUMBER not null, NAME VARCHAR2(200));
2. Insert a data entry
- Insert into test (ID, NAME) VALUES (1, '20140901 ');
- Commit;
INSERT INTO TEST(ID,NAME) VALUES (1,'1111');commit;
3. Create a materialized view log table in the source database
- 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
- Create database link DBLINK_TEST
- Connect TO username identified by "123456"
- Using '(DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = orcl)
- )
- )';
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.
- Create materialized view MV_TEST
- Refresh fast
- On demand
- With rowid
- 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
- Call dbms_mview.refresh ('mv _ test ');