Error Creating Database Link---Please Help

Hi,
I have 2 databases on the same machine and they are called sid7 and sid8.

The service names for them are
louise_company and louise_news.

Now I am trying to connect a database link in the company database(sid7) which will point to the news_owner user in the sid8 database.

This is the command I am giving to create the link

SQL> CREATE PUBLIC DATABASE LINK "NEWS_DBLINK" CONNECT TO
2 "NEWS_OWNER" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS' ;

Database link created.

BUT WHEN i TRY TO USE THE DATABASE LINK IT GIVES ME THE FOLOWING ERROR

SQL> select * from company@NEWS_DBLINK
2 /
select * from company@NEWS_DBLINK
*
ERROR at line 1:
ORA-02085: database link NEWS_DBLINK.HEROLD.COM connects to SID8.WORLD

The error message indicates that

ORA-02085 database link name connects to name

Cause The database link attempted to connect to a database with a different name. The name of the database link must be the same name as the name of the database.
Action Create a database link with the same name as the database to which it connects.

Why should I be forced to give the link name same as the database name.

Infact I even tried that but stil it doesnt work. Please Help

Thanks
Anurag

Try :

CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

OR

CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

I forget exactly which syntax it wants, but it is getting
confused because of the domain name. Play around with it
a bit and please post whichever one works when you find out
so the rest of us can remember!

-John

Quote:

---

Originally posted by jdorlon
Try :

CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

OR

CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

I forget exactly which syntax it wants, but it is getting
confused because of the domain name. Play around with it
a bit and please post whichever one works when you find out
so the rest of us can remember!

-John

---

Hi John,
I tried the following 3 syntaxes but still it does not work

CREATE PUBLIC DATABASE LINK "TEST" CONNECT TO "NEWS_OWNER"
IDENTIFIED BY "sid8" USING 'LOUISE_NEWS' ;


CREATE PUBLIC DATABASE LINK "TEST1" CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

CREATE PUBLIC DATABASE LINK "TEST2" CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

Also the db_domain parameter in both the databases is set to World.

Also I noted one thing that I am able to create a db link from sid8 to sid7 like thes and it is working fine

CREATE PUBLIC DATABASE LINK "TEST2" CONNECT TO "COMPANY" IDENTIFIED BY "company" USING 'LOUISE_COMPANY'

why is it not allowing me to use the link from sid7 to sid8 then.

Please suggest.

Thanks a Lot
Anurag

[Edited by anuragmin on 07-23-2001 at 11:15 AM]

what is after connect to is the username on the distant db, I guess you do not have a user that is called News_owner.herold.com !!!
what you should do is :
create database link ...
connect to user
identified by pwd
using 'sid8.world'

The user i am trying to connect to is called news_owner.

I tried this also but it does not work.

CREATE PUBLIC DATABASE LINK "TEST4" CONNECT TO "NEWS_OWNER"
IDENTIFIED BY "sid8" USING 'SID8.WORLD'

Actually its ok that it is not working because i have no service name defined as sid8.world. The service name I have defined on the server side tnsnames.ora is louise_news and even with that it does not work.


Please Help.

Thanks
Anurag

Set your GLOBAL_NAMES init.ora parameter to false and it will work. You can also test it using "ALTER SESSION SET GLOBAL_NAMES=FALSE".

-amar

OK, sorry for my first incorrect post. I just tested it,
and I can get it to work on my database like this

Create Public Database Link SID8.world
connect to news_owner
Identified by sid8
using 'SID8'
/

Then, when you use it, specify it like this :

select * from dual@sid8.world;

-John

I tried using the exact same command also but it says

SQL> select * from company@sid8.world;
select * from company@sid8.world
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

and thats right because the service name pointing to the database is louise_news and not sid8.

btw which service names do the database links use...
clients side or the servers side.

Thanks
Anurag

I created a link called sid8.world and provided the service name as louise_news and now it is giving me the following error.


SQL> select * from company@sid8.world;
select * from company@sid8.world
*
ERROR at line 1:
ORA-12640: Authentication adapter initialization failed

I dont know what settings i changed but i was playing around with sqlnet.ora to fix the link creation problem and now this error is coming.

Please help me out with the db link problem as i cannot take help from the metalink site as it is down.

Thanks
Anurag

Try this.

Create Public Database Link SID8.world
connect to news_owner
Identified by sid8
using 'LOUISE_NEWS'
/

then select the values from company table.

select * from company@sid8;

Sree_sri , if you take a look at my first post this is exactly what I did but got an error.

well anyways I was able to find a solution for the problem.

It was because global_names was set to true in my sid7 database and was false in the sid8 database hence this conflict.

I set it to false and it works fine now.

Thanks a lot for your patience and helping me out.

Anurag