数据链脚本:
数据库建好以后,需要在A数据库和B数据库中建立双向的数据库链。
A到B的数据库链:
CREATE DATABASE LINK [to_transdb] ——to_transdb是数据库链的名称,不要改;
CONNECT TO [B数据库用户名,如:unicom]
IDENTIFIED BY [密码,如:unicom]
USING ['A的数据库名,如:SDHDB']
B数据库到A的数据库链:
CREATE DATABASE LINK [to_customerdb] ——to_customerdb是数据库链的名称,不要改;
CONNECT TO [A数据库用户名,如:consumer]
IDENTIFIED BY [密码,如:consumer]
USING ['B数据库名,如:zjdb']
实例:B到A的数据库链
CREATE DATABASE LINK to_customerdb
CONNECT TO consumer
IDENTIFIED BY consumer
USING 'zjdb' ——假如B数据库名是zjdb
同一个实例下,有两个用户,这两个用户表数据间的同步。
各自用自己的用户登录,创建数据库链。
然后在需要同步的表上,创建触发器:
insert 同步。(示例)
create or replace trigger t_insert_equippack
after insert on equippack
for each row
declare
begin
insert into res_equippack@to_customerdb
(EQUIPCODE, FRAMESERIAL)
values
(:new.EQUIPCODE,:new.FRAMESERIAL);
exception
when others then
null;
end t_insert_equippack;
update同步。(示例)
create or replace trigger t_update_equiplogicport
after update on equiplogicport
for each row
declare
begin
update res_equiplogicport@to_customerdb
set equipcode = :new.equipcode,
frameserial = :new.frameserial,
slotserial = :new.slotserial,
packserial = :new.packserial,
portserial = :new.portserial
where logicport = :new.logicport;
exception
when others then
null;
end t_update_equiplogicport;
delete同步。(示例)
create or replace trigger t_delete_equipment
after delete on equipment
for each row
declare
begin
delete from res_equipment@to_customerdb where EQUIPCODE = :new.equipcode;
exception
when others then
null;
end t_delete_equipment;
--------------------------------------------------------------------
to_customerdb是另个用户建立的数据库链,res_equippack另一个用户的表
两个表中的字段类型,是需要一样的。只要列出需要的数据,两边对应起来就OK。
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.154.20.09)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdh)
)
)
如果两个数据库实例不在一台机器上。using '上述代码'
其中HOST是你要链接的数据库所在IP,SERVICE_NAME是你要链接的数据库的服务名