Using oracle snapshot dblink to solve database table synchronization problems

Unidirectional and bidirectional synchronization are available.

-- Glossary: Source-database to be synchronized
Purpose -- database to be synchronized

The first six steps must be performed. After 6th, some auxiliary information will be provided.

-- 1. Create dblink on the target database
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 the remote database name.

-- 2. Create tables to be synchronized on the source and target databases (it is best to have primary key constraints so that snapshots can be refreshed quickly)
Drop table test_user;
Create table test_user (id number (10) primary key, name varchar2 (12), age number (3 ));

-- 3. Test dblink on the target database
Select * from test_user @ dblink_orc92_182; // query the source database table.
Select * from test_user;

-- 4. Create a snapshot log for the table to be synchronized in the source database
Create snapshot log on test_user;

-- 5. Create a snapshot and create a snapshot on the target database
Create snapshot sn_test_user as select * from test_user @ dblink_orc92_182;

-- 6. Set the snapshot refresh time (only one refresh method can be selected. It is recommended to use Quick refresh so that the trigger can be used for Bidirectional synchronization)
Quick refresh
Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;
-- Oracle will automatically and quickly refresh immediately, and will not stop refreshing in the future. It can only be used during testing. Real projects must correctly weigh the refresh time.

Completely refresh
Alter snapshot sn_test_user refresh complete Start with sysdate + 30/24*60*60 next sysdate + 30/24*60*60;
-- Oracle automatically performs the first full refresh after 30 seconds, and then completely refreshes every 30 seconds.

-- 7. manually refresh the snapshot. You can manually refresh the snapshot if it is not automatically refreshed.
Manual refresh method 1
Begin
Dbms_refresh.refresh ('sn _ test_user ');
End;

Manual refresh method 2
EXEC DBMS_SNAPSHOT.REFRESH ('sn _ test_user ', 'F'); // The first parameter is the snapshot name, and the second parameter F is the quick refresh. C is the full refresh.

-- 8. Modify the session time format
Alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss ';

-- 9. view the last snapshot refresh time
Select name, LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

-- 10. view the next snapshot execution time
Select last_date, next_date, what from user_jobs order by next_date;

-- 11. Print debugging information
Dbms_output.put_line ('use' | 'plsql ');

-- 12. if you only want one-way synchronization, create the following triggers in the target database (when the source database table changes, the target database table changes, but the target database table changes, the source database table does not change ).
Create or replace trigger TRI_test_user_AFR
After insert 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 two-way synchronization, perform the first six steps in the source database and create the following triggers on both sides. (when the source database table changes, the target database table changes accordingly, the source database table also changes when the target database table is changed)
Create or replace trigger BST114.TRI _ TEST_USER_AFR
AFTER DELETE OR INSERT OR UPDATE
ON BST114.SN _ TEST_USER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Declare
Tmp_id number (10): =-1;
Begin

Dbms_output.put_line ('begin ');
If inserting then
-- Select id into 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 endless loops of the two-way synchronization trigger, add some judgments to the trigger to prevent the endless loop.

-- Above synchronization Principle
1. Create a dblink to access the remote database.
2. Create a local snapshot to map the remote data table. When the remote data table changes, it will be reflected in the snapshot.
3. Because snapshots are similar to visual charts, a trigger is created locally for a snapshot. When the snapshot changes, the corresponding event is triggered.
4. Write the data synchronization code in the trigger.

-- Appendix: Snapshot refresh time parameter description
The number of seconds in a day = 24 hours * 60 minutes * 60 banknotes
So to refresh in 30 seconds, you should write sysdate + 30/(24*60*60) as the parameter)
1 minute = sysdate + 60/(24*60*60)

Minutes per day = 24 hours * 60 minutes
You can write sysdate + 1/(24*60) in one minute)
30 minutes = sysdate + 30/(24*60)
60 minutes = sysdate + 60/(24*60)

And so on
1 hour = sysdate + 1/24 = sysdate + 60/(24*60)
1 day = sysdate + 1
One month = sysdate + 30