Data Synchronization Scenarios:
--Create a link to the desired synchronization table with Dblink
------------------------------------------------------------------------------------------------
--Parameters
--dblink1 Name
--csys User Name
--**** User Password
--host server Address
--connect_data instances
------------------------------------------------------------------------------------------------
--Create Dblink
Create DATABASE link Dblink1
Connect to Csys identified by * * * *
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.5.134) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)‘;
-----------------------------------------------------------------------------------------------
--You need to add the following configuration to the file path D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.8) (PORT = 1521))
)
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
----------------------------------------------------------------------------------------------
--Delete Dblink
DROP DATABASE link dblink1;
--Instructions for use codevalue is a table DBlink1 is created Dblink
Select *from [email protected];
--View all dblink connections in the database
Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';
-----------------------------------------------------------------------------------------------
--Create materialized views
--synchronization of the main table
--The primary key exists in the synchronization main table
--Create materialized view logs
Create materialized view Log on CSB1 with primary key;
--Querying materialized view logs
SELECT * from MLOG$_CSB1;
--synchronous Main table does not exist primary key
--Create materialized view logs
Create materialized view Log on csb1 with ROWID;
-------------------------------------------------------------------------------------------------
--base table creation materialized view (based on materialized view timed refresh--10 minutes)
--there is a primary key
Create materialized view mv_csb1 refresh fast start with Sysdate next trunc (sysdate, ' mi ') +10/1440 as SELECT * FROM [email protected]_cs.regress.rdbms.dev.us.oracle.com;
--there is no primary key
Create materialized view mv_csb1 refresh fast with rowID start with Sysdate next trunc (sysdate, ' mi ') +10/1440 as SELECT * F Rom [email protected]_cs.regress.rdbms.dev.us.oracle.com;
--Querying materialized views
SELECT * from Dba_jobs;
---------------------------------------------------------------------------------------------------------------
--Create materialized views (based on job timed refresh)
Remove the time parameters from the above
--Job timing tasks
--CREATE PROCEDURE
Create or replace procedure REFRESH_MV
As
Begin
Dbms_mview.refresh (' mv_csb1 ');
End REFRESH_MV;
--Set the relevant parameters in the Dbms_job System package
--what value
Begin Refresh_mv;end;
--Interval
Trunc (sysdate, ' mi ') +10/1440
--------------------------------------------------------------------------------------------------------------
Oracle Data Synchronization Solution