dblink 同步数据_利用Oracle DBLink进行表同步

场景:从Oracle A  同步表到Oracle B

一、创建database link(B端)

Sql代码

createpublicdatabaselink dblink_name

connecttoA_user_name identifiedbyA_user_password

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = aa.bb.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)';create public database link dblink_name

connect to A_user_name identified by A_user_password

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = aa.bb.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)';

二、创建snapshot(B端)

Sql代码

Createsnapshot tb_nameasselect*fromtb_name@dblink_name;Create snapshot tb_name as select * from tb_name@dblink_name;

三、修改snapshot刷新时间(B端)

Sql代码

Altersnapshot tb_name refresh complete Startwithsysdatenexttrunc(sysdate+1)+20/24;

--每天20点刷新Alter snapshot tb_name refresh complete Start with sysdate next trunc(sysdate+1)+20/24;

--每天20点刷新

要手动刷新的话

Sql代码

dbms_refresh.refresh('"B"."TB_NAME"');dbms_refresh.refresh('"B"."TB_NAME"');

查看最后一次刷新时间

Sql代码

SELECTNAME,LAST_REFRESHFROMALL_SNAPSHOT_REFRESH_TIMES;