Cannot create database link

Hi! All,

I have one database instance(8.1.7) on w2k and the other on Solaris (8.1.6)
When I creating a database link from w2k to solaris it is giving the following error

SQL> create public database link epro connect to epro1 identified by epro1 using 'epro';

Database link created.

SQL> desc company@epro
ERROR:
ORA-02019: connection description for remote database not found

My net connect is working fine here because from w2k sqlplus I can connect to solaris Database.

Any ideas??

sure you have epro not epro.world in your tnsnames.ora ??
I know I once had a problem (but it was between 7 and 8i) : desc couldn't be done, but selects worked fine. if you do :
select * from company@epro, what do you get ???

Hey
take never the db alias. Take the right db-name. look therefor in the init.ora of the db on the solaris.

I hope than it will run

Hi!
I played safe the alias name and the database name is same i.e epro
I tried out
select * from company@epro;
It gave the following error

SQL> select * from company@epro;
select * from company@epro
*
ERROR at line 1:
ORA-02085: database link EPRO.US.ORACLE.COM connects to EPRO

Then I tried
SQL> create public database link epro connect to epro1 identified by epro1 using 'EPRO.US.ORACLE.COM';

and it gave the following error
SQL> select * from company@epro;
select * from company@epro
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

Guys DB link used to simple in Oracle7.

sure but since then Oracle Names has come out !!!
ahh good ol' 7 time ;))
with names, isn't a DB*Link supposed to have exactly the name of the db it references ???
do you have something in your sqlnet.ora file ?? I mean if you do not work for Oracle, epro should not reference epro.us.oracle.com, guess you have to define something like db_domain=world ...

I am not using Oracle Names server and my db_domain is as default, I have not changed it. So on default config it should work

There's also a bug in db_links where you can't describe a table across a db_link, but you can select count(*) across that link...

Well now I got the following error

SQL> select count(*) from company@epro;
select count(*) from company@epro
*
ERROR at line 1:
ORA-02085: database link EPRO.US.ORACLE.COM connects to EPRO

creating the link with name EPRO.US.ORACLE.COM probably slvesyour last error

it's strange to have US.ORACLE.COM as domain, it looks like a default value, but I think this is the problem ... guess it comes from sqlnet.ora

If push comes to shove this will work. I've had the same problem(s) from NT to Solaris myself.

I've had to give the complete description in the "using" part of the create db_link statement.

So it ended up looking like this:

Create public database link epro
connect to epro1 identified by password
using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = Solaris host-name)(PORT = 1521))
(CONNECT_DATA = (SID = sid name from solaris)))';

(The description is from the database you are trying to connect to, in this case its the SUN Box).

This has and is working for me.....

Charles

Hi! Charles
I tried out and here's the result
SQL> ed
Wrote file afiedt.buf

1 Create public database link epro
2 connect to epro1 identified by password
3 using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
4 (HOST = 10.31.8.85)(PORT = 1521))
5* (CONNECT_DATA = (SID = EPRO)))'
SQL> /

Database link created.
SQL> select count(*) From company@epro;
select count(*) From company@epro
*
ERROR at line 1:
ORA-02085: database link EPRO.US.ORACLE.COM connects to EPRO

Shall I call this frustating or interesting???

connect as sys and query props$ this will tell what are your domains, db name etc

Hi,

Select the table global_name .. and update the Record to
the SID name.

update global_name set global_name='SID';

...

Quote:

---

Originally posted by sudip
Well now I got the following error

SQL> select count(*) from company@epro;
select count(*) from company@epro
*
ERROR at line 1:
ORA-02085: database link EPRO.US.ORACLE.COM connects to EPRO

---

Dear Sudip,

Change the global_names parameter to false in init.ora parameter file and then restart your database.

Then issue the select * from company@epro and check.

This will avoid the above based error.

Yess!!! Changing the Global_name worked.

Thanx a lot Guys.