Resolving database table synchronization problems with Oracle Snapshot Dblink

This example has been completely tested, one-way, bidirectional synchronization can be used.

--Noun Description: Source--The database being synchronized
Purpose-the database to sync to

The first 6 steps must be performed, after the 6th is some auxiliary information.

--1, on the destination database, create Dblink
Drop public database link dblink_orc92_182;
Create public DATABASE LINK dblink_orc92_182 CONNECT to bst114 identified by password USING ' orc92_192.168.254.111 ';
--dblink_orc92_182 is dblink_name.
--BST114 is username.
--password is password.
--' orc92_192.168.254.111 ' is a remote database name

--2, create tables to synchronize on source and destination databases (preferably with primary key constraints, snapshots can be refreshed quickly)
drop table Test_user;
CREATE TABLE Test_user (ID number (a) primary key,name varchar2 (), age number (3));

--3, on the destination database, test the Dblink
SELECT * from test_user@dblink_orc92_182; Queries are tables of the source database
SELECT * from Test_user;

--4, on the source database, create a snapshot log to synchronize the table
Create snapshot log on Test_user;

--5, create snapshots, and create snapshots on the destination database
Create Snapshot Sn_test_user as select * from test_user@dblink_orc92_182;

--6, set snapshot refresh time (only one Refresh method is recommended, use quick refresh, so that you can synchronize with triggers bidirectional)
Quick Refresh
Alter snapshot Sn_test_user Refresh fast Start with Sysdate next sysdate with primary key;
--oracle immediately automatic quick refresh, the subsequent non-stop refresh, can only be used when testing. Real projects should weigh the refresh time correctly.

Full Refresh
Alter Snapshot Sn_test_user Refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;
--oracle automatically refreshes for the first time after 30 seconds, and then refreshes every 30 seconds

--7, manually refreshing the snapshot, without automatic refresh, you can manually refresh the snapshot.
Manual Refresh Mode 1
Begin
Dbms_refresh.refresh (' Sn_test_user ');
End

Manual Refresh Mode 2
EXEC Dbms_snapshot. REFRESH (' Sn_test_user ', ' F '); The first parameter is the snapshot name, and the second parameter F is a quick refresh of C, which is a full flush.

--8. Modify session Time Format
ALTER session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

--9. View the last refresh time of the snapshot
SELECT Name,last_refresh from All_snapshot_refresh_times;

--10. View next execution time of the snapshot
Select Last_date,next_date,what from User_jobs order by Next_date;

--11. Printing Debugging information
Dbms_output.put_line (' Use ' | | ') Plsql ');

--12. If you only want one-way synchronization, then create the following triggers in the destination database (the destination database table changes when the source database table changes, but the source database table does not change when the destination database table changes).
Create or Replace Trigger TRI_TEST_USER_AFR
After inserts or update or delete on Sn_test_user
For each row
Begin
If deleting then
Delete from Test_user where id=:old.id;
End If;
If inserting then
Insert into Test_user (id,name)
VALUES (: New.id,:new.name);
End If;
If updating then
Update Test_user set name=:new.name where id=:old.id;
End If;
End Tri_test_user_afr;

--13. If you want to sync in both directions, perform the first 6 steps in the source database and create the following triggers on both sides (when the source database table changes, the destination database table changes and the source database table changes)
CREATE OR REPLACE TRIGGER BST114. Tri_test_user_afr
After DELETE or INSERT or UPDATE
On BST114. Sn_test_user
Referencing new as new as old
For each ROW
Declare
TMP_ID Number (10): =-1;
Begin

Dbms_output.put_line (' begin ');
If inserting then
--select ID into the tmp_id from Test_user where id=:new.id;
For P in (select ID from Test_user where id=:new.id)
Loop
Tmp_id:=p.id;
End Loop;

Dbms_output.put_line (tmp_id| | ' ===------------');
if (tmp_id=-1) then
Insert into Test_user (id,name,age)
VALUES (: new.id,:new.name,:new.age);
End If;
End If;

If updating then
Dbms_output.put_line (' updated ');
For P in (select Name,age from Test_user where id=:old.id)
Loop
if (p.name!=:new.name) or (p.age!=:new.age) then
Update Test_user set name=:new.name,age=:new.age where id=:old.id;
End If;
End Loop;
End If;

If deleting then
Dbms_output.put_line (' deleted ');
Delete from Test_user where id=:old.id;
End If;
Dbms_output.put_line (' End ');
End Tri_test_user_afr;
--To prevent a bidirectional synchronization trigger from dead loops, add some judgment to the trigger to prevent the dead loop.

--The above synchronization principle
1. First create a dblink, you can access the remote database
2. Create a snapshot locally, map the remote datasheet, and react to the snapshot when the remote datasheet changes.
3. Because a snapshot is similar to a view chart, a trigger is created locally for the snapshot, and the corresponding event is triggered when the snapshot is changed.
4. Code to write synchronization data in triggers.

--Attached: Snapshot Refresh Time parameter description
The number of seconds in a day = 24 hours * 60 Minutes *60
So to refresh in 30 seconds, the parameter should be written like this sysdate+30/(24*60*60)
1 min ==sysdate+60/(24*60*60)

Number of minutes in a day = 24 hours * 60 Minutes
One minute you can write sysdate+1/(24*60)
30 min ==sysdate+30/(24*60)
60 min ==sysdate+60/(24*60)

Analogy
1 hours ==sysdate+1/24==sysdate+60/(24*60)
1 days ==sysdate+1
One months ==sysdate+30