利用oracle快照dblink解決資料庫表同步問題

本執行個體已完全通過測試,單向,又向同步都可使用.

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

前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;
 --為防止雙向同步觸發器死迴圈,所以要在觸發器中增加一些判斷,阻止死迴圈.

--以上同步原理
1.首先建立一個dblink,可以訪問遠端資料庫
2.在本地建立一個快照,映射遠端資料表,當遠端資料表有變化時,會反應到快照中.
3.由於快照類似於視圖表,所以在本地為快照建立一個觸發器,當快照有變化時,會觸發相應事件.
4.在觸發器中寫同步資料的代碼.

--附:快照重新整理時間參數說明
一天的秒數=24小時*60分鐘*60鈔
所以要想在30秒後重新整理,參數應該這樣寫 sysdate+30/(24*60*60)
1分鐘==sysdate+60/(24*60*60)

一天的分鐘數=24小時*60分鐘
一分鐘也可以這樣寫 sysdate+1/(24*60)
30分鐘==sysdate+30/(24*60)
60分鐘==sysdate+60/(24*60)

以此類推
1小時==sysdate+1/24==sysdate+60/(24*60)
1天==sysdate+1
一個月==sysdate+30