Use dblink to synchronize Oracle databases

Dblink is used to perform regular synchronization from Oracle to oracle. A synchronization just made on the card center is used as an example.

Recommended reading:

Use Oracle DBLink for table Synchronization

Use Oracle DBLink

Oracle accesses GreenPlum through DBLink

ORA-01017/ORA-02063 DBLink build Error Problem Analysis and Solution

Oracle create DBLink error: ORA-01017, ORA-02063

1. Create the database to be connected to by dblink (this is not required if it already exists)
 
Example:
View dblink: select * from dba_db_links;
 
Create dblink:
 
Create database link ECARDRYXX
Connect to WPENG
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 202.120.85.118) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = ORA92)
)
)';
 
 
2. Create a view that needs to be synchronized to the Peer Data:
 
Example:
Create or replace view ryxx
Select ryjbxx. userid outid, xyzzjg. bmmc college, xszzjg. bmmc category name, ryjbxx. major,
Ryjbxx. typeof, ryjbxx. sfsf, ryjbxx.cn, ryjbxx. email, decode (sys_user.STATUS, '2', 'yes', '4', 'No', 'no') inetUserStatus
From
(
Select t1.xh userid, t1.xym, t1.xsm, t2.zymc major,
'Student 'typeof, t1.sfsf sfsf, t1.xm cn, t1.dzxx email
From idc_u_xs.xs_xsjbxx t1, idc_u_0000mis.0000_zyxx t2
Where t1.zyh = t2.zydh (+) -- undergraduate
Union all
Select t1.xh userid, t1.xym, t1.xsm, t2.zymc, 'student 'typeof, ''sfsf, t1.xm cn, t1.email
From idc_u_yjs.yjs_yjsjbxx t1, idc_u_yjs.yjs_dict_bsxkzydm t2
Where t1.zydm = t2.zydm (+) -- graduate student
Union all
Select t1.zgh userid, ''xym, ''xsm, ''major, 'teacher' typeof, ''sfsf, t1.xm cn, t1.dzxx email
From idc_u_rs.rs_zzryjbxx t1-in-service personnel
Union all
Select t1.zgh userid, ''xym, ''xsm, ''major, 'teacher' typeof, ''sfsf, t1.xm cn, t1.dzxx email
From idc_u_rs.rs_xjrylsb t1 -- add a person
) Ryjbxx, idc_u_pub.sys_user, idc_u_rs.rs_zzjg xyzzjg, idc_u_rs.rs_zzjg xszzjg
Where
Ryjbxx. userid = sys_user.userid
And ryjbxx. xym = xyzzjg. bmbh (+)-School Organization
And ryjbxx. xym = xszzjg. bmbh (+)-department and organization

3. Create the pl/SQL to be synchronized:
 
For example:
To use more modular and centralized data, we recommend that you write pl/SQL statements for the same database in the same package.
 
CREATE OR REPLACE PACKAGE ECARD IS
Procedure ryxxsync;
END;
 
CREATE OR REPLACE PACKAGE BODY ECARD IS
PROCEDURE RYXXSYNC IS
BEGIN
Delete from wpeng. ryxx @ ecardryxx; -- delete data
Insert into wpeng. ryxx @ ecardryxx (outid, college, catalog name, major, typeof, sfsf, cn, email, inetuserstatus)
Select outid, college, category name, major, typeof, sfsf, cn, email, inetuserstatus from ryxx; -- Insert the latest data
END;
END;
 

4. Create a job and regularly execute synchronization:
 
Example:
Open the SQL command window:
 
Variable ryxxsyncjob number;
 
 
Begin
Dbms_job.submit (: ryxxsyncjob, 'ecard. RYXXSYNC ;',
SYSDATE, 'trunc (SYSDATE) + 1 + 123456'); -- execute it at every day.
Commit;
End;