程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 利用oracle快照dblink來解決數據庫表同步問題

利用oracle快照dblink來解決數據庫表同步問題

編輯：Oracle數據庫基礎

本實例已完全通過測試,單向,雙向同步都可使用.

--名詞說明：源——被同步的數據庫
目的——要同步到的數據庫

前6步必須執行,第6以後是一些輔助信息.

--1、在目的數據庫上，創建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 是dblink_name
--bst114 是 username
--password 是 password
--''orc92_192.168.254.111'' 是遠程數據庫名

--2、在源和目的數據庫上創建要同步的表(最好有主鍵約束,快照才可以快速刷新)
drop table test_user;
create table test_user(id number(10) primary key,name varchar2(12),age number(3));

--3、在目的數據庫上，測試dblink
select * from test_user@dblink_orc92_182; //查詢的是源數據庫的表
select * from test_user;

--4、在源數據庫上，創建要同步表的快照日志
Create snapshot log on test_user;

--5、創建快照，在目的數據庫上創建快照
Create snapshot sn_test_user as select * from test_user@dblink_orc92_182;

--6、設置快照刷新時間(只能選擇一種刷新方式,推薦使用快速刷新,這樣才可以用觸發器雙向同步)
快速刷新
Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;
--oracle馬上自動快速刷新，以後不停的刷新,只能在測試時使用.真實項目要正確權衡刷新時間.

完全刷新
Alter snapshot sn_test_user refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;
--oracle自動在30秒後進行第一次完全刷新，以後每隔30秒完全刷新一次

--7、手動刷新快照,在沒有自動刷新的情況下,可以手動刷新快照.
手動刷新方式1
begin
dbms_refresh.refresh(''sn_test_user'');
end;

手動刷新方式2
EXEC DBMS_SNAPSHOT.REFRESH(''sn_test_user'',''F''); //第一個參數是快照名,第二個參數 F 是快速刷新 C 是完全刷新.

--8.修改會話時間格式
ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'';

--9.查看快照最後一次刷新時間
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

--10.查看快照下次執行時間
select last_date,next_date,what from user_jobs order by next_date;

--11.打印調試信息
dbms_output.put_line(''use ''||''plsql'');

--12.如果你只想單向同步,那麼在目的數據庫創建以下觸發器(當源數據庫表改變時,目的數據庫表跟著改變,但目的數據庫表改變時,源數據庫表不改變).
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.如果你想雙向同步,請在源數據庫中執行前6步,並在雙方都創建以下觸發器(當源數據庫表改變時,目的數據庫表跟著改變,目的數據庫表改變時,源數據庫表也改變)
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;
--為防止雙向同步觸發器死循環,所以要在觸發器中增加一些判斷,阻止死循環.