Wednesday, November 17, 2010

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


SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE



SQL> create database link orcl.local connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select * from dual@orcl.local;
select * from dual@orcl.local
*
ERROR at line 1:
ORA-02085: database link ORCL.LOCAL connects to ORCL.LAB.DBAGLOBE.COM


SQL> drop database link orcl.local;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl';
create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier



SQL> create database link orcl.lab.dbaglobe.com@loopback
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@loopback;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@loopback;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com@l
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@l;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@l
2 ;

Database link dropped.