oracle 数据库 dblink怎么用

创建公开数据库链接

CREATE

PUBLIC

DATABASE

LINK

“数据库链接别名”

CONNECT

TO

“登陆用户”

IDENTIFIED

BY

“登陆密码”

USING

'(DESCRIPTION

=(ADDRESS_LIST

=(ADDRESS

=(PROTOCOL

=

TCP)(HOST

=

IP)(PORT

=

1521)))(CONNECT_DATA

=(SID

=

SID)))'

例如：

CREATE

PUBLIC

DATABASE

LINK

MYDBLINK

CONNECT

TO

USER1

IDENTIFIED

BY

PASSWORD1

USING

'(DESCRIPTION

=(ADDRESS_LIST

=(ADDRESS

=(PROTOCOL

=

TCP)(HOST

=

10.10.10.10)(PORT

=

1521)))(CONNECT_DATA

=(SID

=

ORA10)))'

为方便完成指定数据表的同步 *** 作，可以采用dblink与merge结合的方法完成。

*** 作环境： 此数据库服务器ip为192.168.196.76,有center与branch两个库，一般需要将center的表数据同步到branch，center为源库，branch为目标库，具体步骤如下：

1.在源库创建到目标库的dblink

create database link branch --输入所要创建dblink的名称，自定义

connect to dbuser identified by “password” --设置连接远程数据库的用户名和密码

using '192.168.196.76/branch' --指定目标数据库的连接方式，可用tns名称

在创建dblink时，要注意，有时候可能会报用户名和密码错误，但实际上我们所输入的账户信息是正确的，此时就注意将密码的大小写按服务器上所设置的输入，并在账号密码前号加上双引号(服务器版本不同造成的)。

2.成功后验证dblink

select * from tb_bd_action@branch--查询创建好的brach库

正常情况下，如果创建dblink成功，可采用该方式访问到远程数据库的表.

3.通过merge语句完成表数据同步

此例中需要将center库中的tb_sys_sqlscripe表同步到branch，简单的语法如下:

merge into tb_sys_sqlscripe@branch b using tb_sys_sqlscripe c on (b.pk=c.pk) --从center将表merge到branch,同步的依据是两个表的pk

when matched then update set b.sqlscripe=c.sqlscripe,b.author=c.author --如果pk值是相同则将指定表的值更新到目标表

when not matched then --如果pk值不一至，则将源表中的数据整条插入到目标表中

insert values (c.pk, c.fk, c.createtime, c.lastmodifytime,c.author,c.module,c.deleteflag, c.scripttype)

commit --记得merge后必须commit,否则更改未能提交

4.为方便每次需要同步时自动完成同步工作，可将该语句做成存储过程或脚本来定时执行或按要求手动执行,简单说一下创建脚本的方法:

a.创建merge文件夹

b.先将merge语句写完整后，存到merge.sql文件中

c.新建merge.bat文件，编辑后写入以下内容

sqlplus user/password@serverip/database @"%cd%\merge.sql"

oracle：系统redhat 5.5 数据库：11.2.0.1

mysql：系统centos6.3 数据库：5.6.21

1.先装 mysql-connector-odbc和unixODBC

[root@rac1 ~]# rpm -qa | grep mysql

mysql-5.0.77-4.el5_4.2

mysql-5.0.77-4.el5_4.2

mysql-connector-odbc-5.1.13-1.rhel5

[root@rac1 ~]# rpm -qa | grep ODBC

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

2.配置 /etc/odbc.ini

[myodbc3]

Driver = /usr/lib64/libmyodbc5.so

Description = MySQL ODBC 5.1 Driver DSN

SERVER = 192.1.1.200

PORT = 3306

USER = bi

Password = 123456

Database = chanpin

OPTION = 3

SOCKET =

charset = utf8

3.配置/etc/odbcinst.ini

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

FileUsage = 1

4.测试连接

[root@rac1 ~]# isql myodbc3 -v

+---------------------------------------+

| Connected!|

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

5.配置oracle环境变量

export ORACLE_BASE=/u01/app/oracle

export GRID_HOME=/u01/grid

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=rac1

export BASE_PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$BASE_PATH:/usr/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

ODBCINI=/etc/odbc.iniexport ODBCINI

ODBCSYSINI=/etcexport ODBCSYSINI

ODBCINSTINI=/etc/odbc.ini

export ODBCINSTINI

6.配置监听

listener.ora:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))

)

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(PROGRAM = dg4odbc)

(SID_NAME= myodbc3)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib:/usr/lib64)

)

)

tnsname.ora:

myodbc3=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))

(CONNECT_DATA =

(SID = myodbc3))

(HS = OK)

)

7.配置odbc监听

路径：$ORACLE_HOME/hs/admin

注意：名字要跟odbc配置的名字一样 我这里是myodbc3

[oracle@rac1 admin]$ cat initmyodbc3.ora

HS_FDS_CONNECT_INFO = myodbc3

HS_FDS_TRACE_LEVEL = ON

HS_FDS_TRACE_FILE_NAME = odbc_test.log

HS_FDS_TRACE_LEVEL = 4

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_FDS_SUPPORT_STATISCTICS = FALSE

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1

HS_FDS_SQLLEN_INTERPRETATION=32

set ODBCINI = /etc/odbc.ini

8.测试下监听

[oracle@rac1 admin]$ tnsping myodbc3

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-NOV-2014 03:02:16

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))

OK (0 msec)

9.创建dblink

SQL>create public database link myodbc connect to "bi" identified by "123456" using'myodbc3'

SQL>select count(*) from "t_user"@myodbc

COUNT(*)

----------

53980

---