Database links

[JMac]

I created a DB link to an instance called SPRODEV.AEL but when I try and use it I get the error message thus:

SQL> select * from table1@sprodev.ael;
select * from table1@sprodev.ael
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

The service name I used in the USING clause when I created the DB link works fine when I just issue a CONNECT user/pwd@servicename command, what can be causing it to fail in a DB link?

Both databases (local and remote) have GLOBAL_NAME parameter set to TRUE and SPRODEV.AEL is the global name of the remote DB.

[JP_KIRAN]

May be a silly question,
Do you have the tnsnames in the source machine pointing to the remote machine?

[JMac]

The error message is what I would expect if I'd logged in incorrectly and specified a service name that didn't exist in the Net*8 configuration.

If I use the same service name that I log in as (e.g. user/pwd@service_name) to create the DB link then it should work ?? No?

But it doesn't and when I try and link I get the error.

[JP_KIRAN]

Sorry its not very clear for me

But to make my point clear...

CREATE DATABASE LINK ABC.XYZ.COM CONNECT TO ABC IDENTIFIED BY XYZ USING 'ABC.XYZ.COM';

Even this works try out in your instance, but Database link tries to connect once you give the request to access information from remote.

Try to connect from your Oracle Server to Remote Server using sqlplus or others.

If it fails then you need to have tnsnames entry in your server.

[julian]

Originally posted by JP_KIRAN
May be a silly question,
Do you have the tnsnames in the source machine pointing to the remote machine?

Well, I would be he has it :-)

Now, from your messages I assume that select * from global_name; returns SPRODEV.AEL (people usually use WORLD, but perhaps your choice of AEL is well-motivated).

Tell me, in sqlnet.ora, is NAMES.DEFAULT_DOMAIN set to AEL?
Bounce the instances with GLOBAL_NAME set to false and add NAMES.DEFAULT_DOMAIN = AEL in sqlnet.ora.

[pras]

Hi Julian,

I am also facing the same prob...What i did is

1.connect to sys/pw
2.Grant create database link to scott.
3.grant create materialized view to scott.
4.then i connected to the scott/pw
5.Next i created a database link using the follo syntax
Create database link am.world connect to scott identified by tiger using 'AM.WORLD';
6.DATABASE LINK IS CREATED.
7.When i tried to create a materialized view using the syntax
CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@am.world;
8.i got the error tns could not resolve the service name.

Pls help me i am working on 8.1.7/win2000.
when i queried the global_name...
sql>select * from global_name;
GLOBAL_NAME
---------------------
ITS.PRAS.COM

As u said i checked the sqlnet.ora
NAMES.DEFAULT_DOMAIN = world

I changed it to
NAMES.DEFAULT_DOMAIN = PRAS.COM

After that i am even unable to make the database link...it give me the follo error
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier.

Pls tell me what will be the prob and where i am going wrong..i will be thankful.

Thanks
pras

[pras]

Hi

I also have a connect string(created from net8assistant) to the remote database and i am able to connect it at sql plus using scott@am/tiger
the name of the connect string in tnsnames.ora is as

am =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.0.0.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = amit.pras.com)
)
)

thanks
pras

[pras]

Hi Julian,

I even tried to create the database link thru DBA Studio..and am able to create it but it is tellink me the the database link is not active..how do i make it active...

Thanks again
pras

[julian]

To Pras:

Leave NAMES.DEFAULT_DOMAIN = world in sqlnet.ora

In tnsnames.ora replace am with am.world

Run: update global_name set global_name=am.world; commit;

drop the dB link and run:

CREATE DATABASE LINK "AM.WORLD" CONNECT TO "username"
IDENTIFIED BY "password" USING '(DESCRIPTION = (ADDRESS_LIST = (
ADDRESS = (PROTOCOL = TCP) (HOST = 1.0.0.30)(PORT = 1521))) (
CONNECT_DATA = (SERVICE_NAME = AM)))';

[pras]

Hi Julian,

Thanks a lot.The database link is created successfully but i am still not able to create the materialized view as mentioned.At the same time i am able to create another table using the database link.
Why am i not able to create the materialized view.

Thanks
pras