利用oracle快照dblink解决数据库表同步

starxzj

　　1、在目的数据库上，创建dblink
　　drop public database link dblink_orc92_182;

　　Create public DATABASE LINK dblink_orc92_182 CONNECT TO bst114>　　--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>　　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 ;where>　　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>
　　for p in(select>　　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>　　loop
　　if (p.name!=:new.name) or (p.age!=:new.age) then

　　update test_user set name=:new.name,age=:new.age where>　　end if;
　　end loop;
　　end if;
　　if deleting then
　　dbms_output.put_line(''deleted'');

　　delete from test_user where>　　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