Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORA-02085: database link DBLINK_NAME connects to ORACLE

Tags:

oracle

dblink

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.

like image 525
MAHESH A SONI Avatar asked Apr 03 '12 07:04

MAHESH A SONI


People also ask

How do I grant a database link in Oracle?

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

How do I know if my Dblink is working?

We can verify public database link using select * from dual@public_db_link; How private db links can be verified by a DBA if application schema's password is not known.

What is DB Link for?

A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.


1 Answers

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.

like image 156
Alex Poole Avatar answered Sep 20 '22 13:09

Alex Poole