oracle dblink

link_name      dblink名

username      对端用户名

password       对端用密码

用户级dblink

 

需要权限create database link,只能在当前用户下使用

grant create database link to user1;

 

以下写法等价

写法1

create database link link_name

connect to username identified by password

using '192.168.10.1:1521/orcl'

 

写法2

vi $ORACLE_HOME/network/admin/tnsnames.ora

增加以下

 orcl =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

create database link link_name

connect to username identified by password

using 'orcl'

 

公用dblink

以下写法等价

写法1

create public database link link_name

connect to username identified by password

using '192.168.10.1:1521/orcl'

 

以下写法等价

写法2

写法2

vi $ORACLE_HOME/network/admin/tnsnames.ora

增加以下

 CONN_orcl =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

 

create public database link link_name

connect to username identified by password

using 'orcl'

 

 

使用测试

tab为对端表名

select * from tab@link_name;