命令如下:
create database link databaseLinkName
connect to userName
identified by password
using databaseServiceName
//常见错误:
1.成功建立,但是没有办法查询出数据,错误为:
ORA-02085
当global_name参数设置为true,则dblink必须命名为和在目标数据库如下查询出的结果一致:
select * from grobal_names;
When the source database initialization parameter GLOBAL_NAMES is set to true, the
database link name must match the target database global name as it exists in the GLOBAL_NAME
view in the data dictionary.
The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command:
SQL>Select * from global_name;
Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.
Check the contents of ALL_DB_LINKS for the fully qualified link name.
For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:
SQL>Create public database link TEST connect to userid identified by password using ?test?;
SQL>select * from tablename@TEST;
This select would yield the following error:
ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"
The correct syntax for defining the link would be:
SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using “test”;
SQL>select * from tablename@TEST.WORLD.COM;
Would yield desired result.
It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.
In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:
Login to TEST with system privileges and issue:
SQL>alter database rename global_name to TEST.ORACLE.COM;
Now, the create database link statement could also be changed.
Login to PROD.
SQL>create public database link TEST connect to userid identified by password using ?test?;
A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.
SQL>select * from tablename@TEST;
This would yield the desired result.
2.无法建立链接:
错误如下:
ORA-02082: a loopback database link must have a connection qualifier
这是因为:
本机的global_name和databaseLinkName相同,
把databaseLinkName该成和global_name不相同的名称就可以了。
分享到:
相关推荐
oracle新建数据库和连接配置步骤;
语句创建ORALCE数据库链接,直接执行,替换数据库实例名即可
OracleSqlDeveloper链接数据库并创建用户,使用指南。
oracle 数据库链接的创建、修改、查看、删除等
NULL 博文链接:https://danlida.iteye.com/blog/729184
oracle建立数据库链接.有关步骤和注释.oracle建立数据库链接.有关步骤和注释.
C#连接Oracle数据库(直接引用dll使用)Oracle.ManagedDataAccess,无需装Oracle数据库
java链接oracle数据库详细代码和main函数测试,希望对下载的网友们有用。
主要介绍了VS2015连接Oracle数据库的详细步骤,需要的朋友可以参考下
教你怎样在SQL-SERVER中创建Oracle的链接服务器,实践摸索出来的经验,用的是SQL-SERVER 2005连接Oracle 10g数据库。
定时导出导入备份数据库 博文链接:https://jxpengtao.iteye.com/blog/178163
NULL 博文链接:https://ljm1227134894.iteye.com/blog/1738009
1.6.1 利用 Oracle 安装程序创建数据库 1.6.2 使用安装程序创建数据库的注意 事项 1.6.3 怎样建立自己的定制数据库创建 脚本 1.6.4 如何从已有数据库克隆数据库 1.6.5 怎样利用 Database Configuration ...
建立和优化数据库文件的方针: 6 监控磁盘I/O的方法: 7 优化回滚段 7 检测回滚段争用: 7 通过以下公式计算等待比率: 8 若任何一个的比率大于1%则建议再创一个回滚段: 8 避免动态分配空间 8 优化REDO日志 8 检测...
介绍通过SQLSERVER链接服务器实现sqlserver和oracle的异构数据库分布式查询。
oracle通过ODBC建立dblink访问SqlServer数据库配置
Oracle数据库链接建立技巧与实例讲解[参考].pdf
本文详细描述了在oracle10g或11g中使用dblink访问sql server,使用oracle的透明网关来访问异构的数据库,方便一些习惯使用oracle的开发人员操作sql server数据库