create database link
2010-04-07 13:34:12| 分类:技术文档 | 标签:|字号大中小 订阅
如果本地的tnsnames.ora中已经建立了远程的数据库连接,那么就是用2,否则就是用一
1:create database link geelyin96
connect to geelyin identified by geelyinpassword
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.1.96)(PORT = 1521))
)
(CONNECT_DA
(SERVICE_NAME = appdb)
)
)';
说明:geelyin96为database link的名字,geelyin为远程数据库的用户名,geelyinpassword 为远程数据库的密码
host为远程数据库的ip,port为远程数据库的端口号,service_name为ssid的名字
2:create database link geelyin96
connect to geelyin identified by geelyinpassword
using '10.86.1.96';
说明:geelyin96为database link的名字,geelyin为远程数据库的用户名,geelyinpassword 为远程数据库的密码,
'10.86.1.96' 为tnsnames.ora中远程连接数据库的链接名字
查询语句sql->select * from tablename@geelyin96
select * from dba_objects where object_type='DATABASE LINK' 查询所有database link
创建数据库链接:
create database link linkname
connect to username
identified by password
using 'connection string';
如果想建立全局链接,则需要使用关键字:public
create public database link linkname
connect to username
identified by password
using 'connection string';
connection string可以有两种形式:
1,在文件tnsnames.ora配置的名称:
如:
CDRDL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.129.29)(PORT = 2521))
)
(CONNECT_DATA =
(SERVICE_NAME = cdrdb)
)
)
此时可以写成:
create database link linkname
connect to username
identified by password
using ' CDRDL';
2,也可以直接使用‘=’号后面的字符串,
如:
create database link linkname
connect to username
identified by password
using ' (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.147.129.29)(PORT=2521)))(CONNECT_DATA=(SERVICE_NAME=cdrdb)))';