库A数据迁移到新库B。
dblink和trigger都在有数据的库中创建:
create database link "dblink名称"
connect to "目标库登录名" identified by "目标库密码"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = "库ip")
(PORT = "库端口")) )
(CONNECT_DATA = (SERVICE_NAME = "服务名") ) )';
--删除dblink:drop database link 名称; 需要在创建的库执行
创建当前库的表与之相对应的映射表实现映射方式①
--drop trigger "删除触发器名称" --删除触发器
CREATE
TRIGGER "触发器名称"
BEFORE INSERT OR UPDATE OR DELETE
ON "当前库要同步的表名"
FOR EACH ROW
declare
pragma autonomous_transaction; --指定自由事务处理 解决触发器中不能提交事务问题
BEGIN
case
when inserting then
insert into "目标库需要同步的表"@"dblink名称"
(
id,
accountno,
officeid
)
values
(
:new.id,
:new.accountno,
:new.officeid
);
when updating then
update "目标库需要同步的表"@"dblink名称"
set
accountno =:new.accountno,
officeid =:new.officeid
where id=:new.id;
when deleting then
delete "目标库需要同步的表"@"dblink名称"
where id =:old.id;
end case;
commit;
END;
创建当前库的表与之相对应的映射表实现映射方式②
CREATE OR REPLACE TRIGGER "触发器名称"
AFTER INSERT OR UPDATE OR DELETE ON "当前库要同步的表名"
FOR EACH ROW
declare
pragma autonomous_transaction;
BEGIN
IF INSERTING THEN
insert into "目标库需要同步的表"@"dblink名称"
(
id,
officeid,
currencyid
)
values
(
:new.id,
:new.officeid,
:new.currencyid
);
ELSIF UPDATING THEN
update "目标库需要同步的表"@"dblink名称"
set
officeid = :new.officeid,
currencyid = :new.currencyid
where id = :new.ID;
ELSIF DELETING THEN
delete from "目标库需要同步的表"@"dblink名称" where id = :old.id;
END IF;
commit;
END;