2个oracle数据库服务器的表数据同步

现有两台Oracle数据库服务器A和B(A,B可以是在同一内网，也可以是在互联网上的两台独立机器)。A和B里有都有testable表，结构一样，现需要当A库中的testable表变化时，B库里的testable也相应变化数据

解决方案：

在A中建立到B库的链接，然后对要同步的表做一个同义synonym，最后建一个触发器，就可以完成了。当然，你所用的当前的用户要有相应的权限去执行这些操作。

当从A向B同步数据时，应该在A上做所有的设置:

1,为保证连接到另一台远程服务器的数据库，你需要建立一个DB Link，但是，这里要注意语法格式，using +"connect string"，这个connect string应该是存在于Oracle服务器的TNSNAMES.ORA文件里，监听程序将从这里获取远方服务器

的ip地址等信息,定义一个test的connect string如下:


test =
(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.235.48.128)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = jyxt)

)

)

把它存到你的TNSNAMES.ORA文件里。
2,然后就可以定义DB Link了：


create public databASE link test01
connect to jyxt identified by "jyxt" using 'test';


3,建立synonym(同义)

create or replace synonym abc for jyxt.T_jysr@test;


建立完了以后，你可以通过:

select * from test01

上面的语句相当于在B服务器上执行：

select * from testable

４，封建触发器：

当A中的testable表变化时（这里只考虑插入操作），就会触发向远程的B库的testable也插入相应的数据：


create or replace trigger test after insert on T_jysr for each row
begin
insert into abc (dw) values (:new.dw);
end;







create or replace trigger test after insert on T_jysr for each row
begin
insert into T_jysr (dw) values ('美国');
end;


ok,现在我们可以测试一下，你在Ａ库中往testable表中插入一条记录，看看Ｂ库中是不是也相应的增加了


本地数据库SID=T2

远程数据库SID=LIFE02

假设你的网络设定无误

1) vi local database tnsname.ora

life02 =
(description =
(address = (protocol = tcp)(host = 192.168.1.1)(port = 1521))
(connect_data = (sid = life02))
)

2)建立属于公开的(public)或者是专属的db link object

SQL> CREATE PUBLIC DATABASE LINK MYTEST
2 CONNECT TO APPLE IDENTIFIED BY APPLE
3 USING 'life02'

Database link created.

3)复制远程数据库的table到本地数据库来

SQL> COPY FROM APPLE/APPLE@LIFE02 -
> CREATE ABC -
> USING SELECT * FROM TEST;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
　
　Table ABC created.

3 rows selected from APPLE@LIFE02.
3 rows inserted into ABC.
3 rows committed into ABC at DEFAULT HOST connection.

SQL> SELECT * FROM ABC;

ID
----------
100
200
333



触发器的类型有:

触发器类型：

1、 语句触发器

2、 行触发器

3、INSTEAD OF触发

4、 系统条件触发器

5、 用户事件触发器

2.1、语句级触发器.(语句级触发器对每个DML语句执行一次)

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。

实例:

create or replace trigger tri_test
after insert or update or delete on test
begin
if updating then
dbms_output.put_line('修改');
elsif deleting then
dbms_output.put_line('删除');
elsif inserting then
dbms_output.put_line('插入');
end if;
end;


2.2、行级触发器.(行级触发器对DML语句影响的每个行执行一次)

实例一:

触发器
行级触发器
create table test(sid number,sname varchar2(20));--创建一个表
create sequence seq_test;--创建序列
create or replace trigger tri_test--创建触发器
before insert or update of sid on test
for each row--触发每一行
begin
if inserting then
select seq_test.nextval into:new.sid from dual;
else
raise_application_error(-20020,'不允许更新ID值!');--中断程序
end if;
end;

测试,插入几条记录
insert into test values(0,'ff');
insert into test values(0,'ff');
insert into test values(0,'tt');


实例二:

创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.
create or replace trigger trig_job
before insert or update of job
on emp
for each row
begin
if inserting then
:new.job:=upper(:new.job);
else
:new.job:=upper(:new.job);
end if;
end;


2.3、instead of触发器.

(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)

语法如下:

　create or replace triggertrig_test
instead ofinsert or update on表名
referencing ne

w as n
for each row
declare
..........
begin
........
end;


2.4、模式触发器.

可以在模式级的操作上建立触发器.


实例如下:　

create or replace trigger log_drop_obj
after drop on schema
begin
insert into .....
end;

　2.5、数据库级触发器.

可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.

实例:

create or replace trigger trig_name
after startup on database
begin
...........
end;


2.6、例子：

需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。　

Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/


即使SYS，SYSTEM用户也不能修改foo表

2.7、[试验]

对修改表的时间、人物进行日志记录。

1、 建立试验表　

　create table employees_copy as select *from hr.employees


2、 建立日志表

create table employees_log(
who varchar2(30),
when date);


3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log表。　

　Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(Who,when)
Values( user, sysdate);
End;
/



　4、 测试

　update employees_copy set salary= salary*1.1;
select *from employess_log;


5、 确定是哪个语句起作用?

即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?

可以在触发器中使用INSERTING / UPDATING / DELETING条件谓词，作判断：

begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;


2.8、[试验]

1、 修改日志表

　alter table employees_log
add (action varchar2(20));


2、 修改触发器，以便记录语句类型。

then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(Who,action,when)
Values( user, l_action,sysdate);
End;Create or replace trigger biud_employee_copy
Before insert or update or delete
On emplo

yees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting
/


3、 测试

insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log