ORACLE_数据同步

这是我之前做过的数据库同步的问题，最近老是看到有人在csdn里问数据库同步的问题，所以我就想把我的实现方案共享一下。多多交流。用到的表是我临时创建的表。有不妥之处请见谅，毕竟是免费的东西。
在本地建立两张表(T1,T2)，这两张表和远程的表结构一样，通过触发器实现数据的同步，然后对本地的两张表进行物化，再在物化视图上建立触发器，实时的修改T表(并没有考虑大字段的情况)。
该方案是测试成功的，源数据库是oracle10.2.0.3.0,目标数据库是oracle10.2.0.3.0,并且做到了数据的实时更新。
1、在源数据库创建database link,确保两台服务器可以连通。
首先在oracle 的E:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora配置文件里添加远程oracle服务器的实例，具体如下：

[quote]
ORCL103 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)[/quote]


然后执行创建database link的语句：
SQL code
[quote]create database link ORCL103.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to CCPPH1
using 'orcl103';[/quote]

然后测试是否已连通，在命令窗口输入：
Select count(*) from T1@orcl103;

2、在本地建表(T1, T2),要和源数据库中的表结构一致。
SQL code[quote]
--T1表
Create table T1 as select * from T1@orcl103;
alter table T1 add constraint PK_ID primary key (ID);

--T2表
Create table T2 as select * from T2@orcl103;
alter table T2 add constraint PK_ID primary key (ID);[/quote]

3、在源数据库上分别建立触发器
SQL code [quote]--T1表触发器
create or replace trigger TRI_T1
after insert or update or delete on T1
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T1@orcl101 where id=:old.id;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T1@orcl101(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T1@orcl101 set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T1;
--T2表触发器
create or replace trigger TRI_T2
after insert or update or delete on T2
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T2@orcl101 where id=:old.id;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T2@orcl101(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T2@orcl101 set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T2;[/quote]


4、在目标数据库创建物化视图日志
--创建物化视图日志
SQL code
[quote]create materialized view log on T1 with rowid;
create materialized view log on T2 with rowid;[/quote]

5、在目标数据库创建物化视图
SQL code
[quote]create materialized view MV_T
refresh fast on commit
as
select t1.rowid as t1rowid,t2.rowid as t2rowid,t1.id,t1.name,t2.id as t2id,t2.name as t2name from T1 t1,T2 t2 where t1.id = t2.id;[/quote]


6、在目标数据库创建基于物化视图的触发器
SQL code
[quote]create or replace trigger TRI_T
after insert or update or delete on mv_T
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T where ID=:old.ID;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T;


[/quote]