dblink 同步数据_利用DBLINK同步表数据库--老刘

把10.10.17.2数据库上的TEST表定时同步到10.10.17.3数据库

1.在10.10.17.3上创建DBLINK指向10.10.17.2数据库

配置TNS信息如下:

[oracle@june3 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/app/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

JUNE =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = june)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

开始创建DBLINK

SQL> create public database link DBL_JUNE connect to test identified by test

2 using 'june';

Database link created.

其中DBL_JUNE 为DBLINK名字

test identified by test 为10.10.17.2的数据库用户名密码

using 'june' june为TNS别名

此时在10.10.17.3上查看：

SQL> desc test.test@dbl_june

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

已经可以访问

2.测试脚本

sqlplus test/test<

drop table test purge;

create table test as select * from test.test@dbl_june;

exit

!