Database links

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 [email protected];
select * from [email protected]
*
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.

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

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.

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.

Quote:

---

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.

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 [email protected];
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

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

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

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)))';

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

Pras: Replace below THE_NAME_OF_THE_TABLESPACE with the real name you use.

On the master site run this:

Code:

---

CREATE SNAPSHOT LOG ON EMP
TABLESPACE THE_NAME_OF_YOUR_TABLESPACE
PCTFREE 60 PCTUSED 30 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

---

In the snapshot site run:

Code:

---

CREATE SNAPSHOT EMP_MV
PCTFREE 10 PCTUSED 40 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE THE_NAME_OF_THE_TABLESPACE
BUILD IMMEDIATE
USING INDEX TABLESPACE THE_NAME_OF_THE_TABLESPACE
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
        FREELISTS 1 FREELIST GROUPS 1 )
REFRESH FAST WITH PRIMARY KEY
START WITH trunc(sysdate) + 4/24
NEXT trunc(sysdate+1) + 4/24
AS
SELECT * FROM VEROKUNTA@AM;

---

Your MV will be refreshed every morning at 4AM.

Hi Julian,

Sorry my mistake...the only prob was i was giving the name of the db link in lower case..It should be in upper case..now i am able to create the materialized view also..Thanks again

Good day..
pras

Hi Julian,

Thanks for the code...U made my day..

pras