1.创建dblink的第一种方式,是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。tnsnames.ora文件在你安装oracle客户端安装文件里 如:(E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN)
创建远程连接:
INT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.163.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ceshidb)
)
)
create public database link to_claim connect to scott identified by tiger using 'INT'; 其中to_claim是你创建的dblink名字,INT是远程数据库的实例名,scott/tiger是登录到远程数据库的用户/密码。然后在本地数据库中通过dblink访问远程数据库INT中c_claim表.
sql语句如下所示: select * from c_claim@to_claim;
2.创建dblink的第二种方式,是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库,直接将配置远程连接的语句放到DBlink创建语句里面
create database link to_claim
connect to scott identified by tiger
using ‘(INT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.163.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ceshidb)
)
)
)’;