Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1106963
  • 博文数量: 227
  • 博客积分: 6860
  • 博客等级: 准将
  • 技术积分: 2688
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-02 16:06
文章分类

全部博文(227)

文章存档

2015年(7)

2014年(8)

2012年(5)

2011年(62)

2010年(145)

分类: Oracle

2010-09-08 21:41:08

1.  在2个实例之间同步表的数据

先创建DBLINK...

create database link TEST.US.ORACLE.COM
  connect to TEST identified by TEST

  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';

测试Db_LINK

select * from

删除数据

truncate table tagentinfo

同步数据
insert into tagentinfo select * from

2.  建立触发器同步数据

建立dblink

create database link TEST2.US.ORACLE.COM
  connect to ICD identified by ICD
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';

创建触发器


create or replace trigger trib_tagentinfo
  after insert or update or delete on tagentinfo
  for each row
declare
  integrity_error exception;
  errno  integer;
  errmsg char(200);
  dummy  integer;
  found  boolean;
begin
  if inserting then
    insert into
      (agentid,
       name,
       agentskills,
       agentworkgroup,
       subccno,
       vdn,
       workgroupid)
    values
      (:NEW.agentid,
       :NEW.name,
       :NEW.agentskills,
       :NEW.agentworkgroup,
       :NEW.subccno,
       :NEW.vdn,
       :NEW.workgroupid);
  elsif updating then
    update
       set name           = :NEW.name,
           agentskills    = :NEW.agentskills,
           agentworkgroup = :NEW.agentworkgroup,
           vdn            = :NEW.vdn,
           workgroupid    = :NEW.workgroupid
  elsif deleting then
    delete from where agentid = :OLD.agentid;
  end if;
exception
  when integrity_error then
    raise_application_error(errno, errmsg);
end;
/

阅读(677) | 评论(0) | 转发(0) |
0

上一篇:使用SecureCRT乱码问题

下一篇:RMAN恢复

给主人留下些什么吧!~~