ORA-02085: database link DBLINK_NAME connects to ORACLE
I m having a database by name 'HBHAWANI' on my domain server name 'HBHAWANI' (version Oracle 8i). Whenever I try to create a dblink here it gives the error :
Link : "DBLINK_DOMAINSERVERTOUPASS_PP"
Error : ORA-02085: database link DBLINK_DOMAINSERVERTOUPASS_PP connects to ORACLE
Script for DBLink is:
CREATE PUBLIC DATABASE LINK DBLINK_DOMAINSERVERTOUPASS_PP
CONNECT TO UPASS.HBHAWANI
IDENTIFIED BY <PWD>
USING 'UPASS.HBHAWANI';
I have tried so much but occurring same error. Kindly provide a suggestion on this.
Answer
You probably have global_names set to true, and you aren't connecting quite how you think. The connect to is the user account on the remote database you want to reach, corresponding to the password you give in the identified by clause. The using looks like a tnsnames.ora entry, but one which has connect_data pointing to a SID of ORACLE; it may be helpful to add that entry to your question. It looks like you're currently giving the alias name in both the using and connect to clauses, which probably isn't correct.
With global_names on, the database link name has to match the remote database name - it's actual name, not the alias you've given to it. This means your database link name will have to be ORACLE, which might be a little confusing.
You could also turn off the global_names setting at the database level, but that may not be allowed or desirable. If this is a rarely-used link you could also add alter session set global_names=false before any queries that use the link. It may be less confusing in the long term to have your names consistent though.