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

Problem Description
You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example.

SQL> create database link tiger;
create database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier


SQL> drop database link tiger;
drop database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Cause of the Problem
This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM

We see the global name of the database is started with tiger(db_name) and the default db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.

Now whenever we try to create a database link named TIGER (without any domain) it takes name as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM (original database link name+ default domain) which is equivalent to global_name of the database and thus error will occur because database link name must not be equal to the global database name.

Solution of the Problem
Two different solution of this problem.
1)Change the database link name so that it is different from global database name.

SQL> create database link tiger.net connect to user_name identified by password using 'TNS_NAME';

Database link created.

2)Change the global name of the database, create/drop database link and then back to global name of the database to the original name.
i)Error when creating database link name with same of global_name.

SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
create database link tiger connect to user_name identified by password using 'TNS_NAME'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

ii)Change the global database name.
SQL> alter database rename global_name to test;
Database altered.

iii)Now dropping the database link tiger will work as now it (TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) is not same as the global name (TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM)

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> drop database link tiger;
Database link dropped.

iv)Also creating the database link with named Tiger (by default take as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) will work.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';

Database link created.

You can check it by,

SQL>  col host for a10
SQL>  set lines 140
SQL>  col owner for a10
SQL>  col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';

OWNER      DB_LINK                                  USERNAME         HOST       CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG    TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM    USER_NAME        TNS_NAME   24-JAN-09
MAXIMSG    TIGER.NET                                USER_NAME        TNS_NAME   24-JAN-09


v)Back to the original global database name.
SQL> alter database rename global_name to tiger;

Database altered.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining
Read more