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

case : scott user wantt to access objects of the sales user in same database by using dblink.(assume he has select any table privilege)


SQL> conn scott/tiger
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

SQL> create database link proddb connect to sales identified by password using 'proddblocal'
create database link proddb connect to sales identified by password using 'proddblocal'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> ed
Wrote file afiedt.buf
1 create database link proddb@proddb
2 connect to sales identified by password
3* using 'proddblocal'
SQL> /

Database link created.

SQL> select count(*) from dual@proddb ;

COUNT(*)
----------
1

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST

CREATED
proddb.sales.ORG@proddb sales password proddblocal

27-APR-07

SQL> conn sales/password@proddblocal
Connected.
SQL> select count(*) from tsales;

COUNT(*)
----------
246678

SQL> conn scott/tiger
Connected.
SQL> select count(*) from tsales@proddb@proddb;

COUNT(*)
----------
246678