ORA-02082: a loopback database link must have a connection qualifier

最新推荐文章于 2022-03-08 17:20:01 发布

娜然

最新推荐文章于 2022-03-08 17:20:01 发布

阅读量2.2k

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/nanaranran/article/details/51915004

版权

oracle 专栏收录该内容

83 篇文章 1 订阅

订阅专栏

做一个创建db link的实验的时候，报的错误，我的实验是为了检查数据库的global_name与db link的关系。

下面是过程和解释：

1.查看数据库的数据库名字

SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string cedb
db_unique_name string cedb
global_names boolean FALSE
instance_name string cedb
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string cedb

2.查看数据库的global_name

SQL> select * from global_name;

GLOBAL_NAME
------------------------------
CEDB

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEDB_HX
MY

3.然后创建db link，名字是cedb
SQL> create database link cedb connect to gw identified by wg using 'cedb';
create database link cedb connect to gw identified by wg using 'cedb'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier -----报错了

4.下面查看这个db link是否生成，没有

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEDB_HX
MY

5.查看这个错误的解释
SQL> host oerr ora 2082
02082, 00000, "a loopback database link must have a connection qualifier"
// *Cause: An attempt was made to create a database link with the same name ---解释说是跟当前的数据库名字重复了，实际上解释的不好，应该是跟global_name一样了
// as the current database.
// *Action: a loopback database link needs a trailing qualifier, for example
// MYDB.EXAMPLE.COM@INST1 - the '@INST1' is the qualifier
//

SQL> alter database rename global_name to cedb1; ---下面修改数据库的global_name为cedb1

Database altered.

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEDB_HX
MY

SQL> show parameter name ---这时候就查看数据库的名字，还是cedb

NAME TYPE VALUE
------------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string cedb
db_unique_name string cedb
global_names boolean FALSE
instance_name string cedb
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string cedb

SQL> create database link cedb connect to gw identified by wg using 'cedb'; ---再次创建，成功

Database link created.

SQL> select * from test@cedb; ---查看，发现是远程表的数据

ID
----------
1
11

SQL> select * from test@cedb1; ---此时，如果查看名称为cedb1的db link，也是有值的，是本地的表数据

ID
----------
1

SQL> select * from test;

ID
----------
1

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEDB
CEDB_HX
MY

实际上是没有cedb1这个link的，这其实是本地的内容。