利用oracle快照dblink来解决数据库表同步问题.doc

前6步必须执行,第6以后是一些辅助信息.oracle db_link 和触发器实现不同数据库表的同步-1、在目的数据库上，创建dblinkdrop public database link dblink_orc92_182;Create public DATABASE LINK dblink_orc92_182 CONNECT TO bst114 IDENTIFIED BY password USING orc92_11;例如：:create database link ciqlink connect to text identified by text using orcl247; -dblink_orc92_182 是dblink_name-bst114 是 username-password 是 password-orc92_11 是远程数据库名-2、在源和目的数据库上创建要同步的表(最好有主键约束,快照才可以快速刷新)drop table test_user;create table test_user(id number(10) primary key,name varchar2(12),age number(3);-3、在目的数据库上，测试dblinkselect * from test_userciqlink; /查询的是源数据库的表select * from test_user;-4、在源数据库上，创建要同步表的快照日志Create snapshot log on test_user;-5、创建快照，在目的数据库上创建快照Create snapshot sn_test_user1 as select * from test_user ciqlink;-6、设置快照刷新时间(只能选择一种刷新方式,推荐使用快速刷新,这样才可以用触发器双向同步)快速刷新Alter snapshot sn_test_user1 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、手动刷新快照,在没有自动刷新的情况下,可以手动刷新快照.手动刷新方式1begindbms_refresh.refresh(sn_test_user);end;手动刷新方式2EXEC 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 rowbegin 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,:); end if; if updating then update test_user set name=: where id=:old.id; end if;end TRI_test_user_AFR;-13.如果你想双向同步,请在源数据库中执行前6步,并在双方都创建以下触发器(当源数据库表改变时,目的数据库表跟着改变,目的数据库表改变时,源数据库表也改变)CREATE OR REPLACE TRIGGER BST114.TRI_TEST_USER_AFRAFTER DELETE OR INSERT OR UPDATEON BST114.SN_TEST_USER REFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWdeclare 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.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 (!=:) or (p.age!=:new.age) then update test_user set 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测试：insert into test_user1 values(2,xiahou,33);insert into test_user1 values(3,xiahou,33);insert into test_user1 values(4,xiahou,33);commit;insert into test_user1 values(5,xiahou,33);insert into test_user1 values(6,xiahou,33);insert into test_user1 values(7,xiahou,33);commit;select * from sn_test_user1;/在目的端查看update