假设有数据库db1和db2 , 表db1.t_task_msg, db2.t_task_msg
现在同步db1.t_task_msg数据到db2.t_task_msg
1, 在db2建立到db1的连接source_link
create database link source_link
connect to db1_user identified by db1_pwd
using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
2,在db1上创建要同步的表的快照日志
create snapshot log on t_task_msg;
3,在db2上创建快照并设置刷新时间
create snapshot sn_t_task_msg
refresh fast Start with sysdate next sysdate with primary key
as select * from t_task_msg@source_link;
或
create snapshot sn_t_task_msg as select * from t_task_msg@source_link;
Alter snapshot sn_t_task_msg refresh fast Start with sysdate next sysdate with primary key;
4,创建一个触发器, 当快照有更新时, 更新db1中的数据
create or replace trigger tr_t_task_msg
after insert or update or delete on sn_t_task_msg
for each row
begin
if deleting then
delete from t_task_msg where fid=:old.fid;
end if;
if inserting then
insert into t_task_msg(fid,fserviceid,fcontent,fuserid,fstate,finserttime)
values(:new.fid,:new.fserviceid,:new.fcontent,:new.fuserid,:new.fstate,:new.finserttime);
end if;
if updating then
update t_task_msg set fserviceid=:new.fserviceid, fcontent=:new.fcontent, fuserid=:new.fuserid, fstate=:new.fstate, finserttime=:new.finserttime where fid=:old.fid;
end if;
end tr_t_task_msg;
----本文来Net探索者,http://www.codefinds.net 转载请说明出处!