oracle中建立数据库链接

命令如下：

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 [e-mail];

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不相同的名称就可以了。