data base link

[dinkar]

WHEN I CREATED DATA BASE LINK FROM ORACLE8I SERVER TO ORACLE8 SERVER,
IAM ABLE TO ACCESS TABLES OF ORACLE8.

BUT WHEN IAM CREATED DATABASE LINK ORACLE8 TO ORACLE8I
IAM UNABLE TO ACCESS TABLE OF ORACLE8I

AND THE ERROR MESG:->TNS:COULD NOT RESOLVE THE SERVICE NAMES

THE SAME ERROR IS COMING WHEN I CREATE LINK FROM ORACLE8 TO ORACLE8 TOO

SO HOW I CAN I DO THAT...

THANKS IN ADVANCE

[adewri]

First try connecting from oracle8 to oracle8i using

sqlplus username(username used in the dblink)/password(password used in the dblink)@connect_string(using 'connect_string' used in the dblink)

try resolving the TNS:COULD NOT RESOLVE THE SERVICE NAMES problem then use the DBLINK

HTH

[abhaysk]

Originally posted by adewri

(using 'connect_string' used in the dblink)

dinkar:

Jus to add...

in the using clause where u specify connect_string....give it as %SID%.%DOMAIN%.

Abhay.

[adewri]

Originally posted by abhaysk
dinkar:

Jus to add...

in the using clause where u specify connect_string....give it as %SID%.%DOMAIN%.

Abhay.

Not necessary.

create database link global_name.domain_name connect to username identified by password using 'connect_string';

where using clause is the connect_string. This may or may not be your sid.domain_name can be any thing, it can be abc or xyz depending on what you have name your connect_string.

[abhaysk]

Originally posted by adewri
Not necessary.

create database link global_name.domain_name connect to username identified by password using 'connect_string';

where using clause is the connect_string. This may or may not be your sid.domain_name can be any thing, it can be abc or xyz depending on what you have name your connect_string.

Amar :

It would be necessary in a situation some thing like this.

SQL*NET

# SQLNET.ORA Network Configuration File: C:\Oracle\Ora9i\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = world

--NAMES.DEFAULT_DOMAIN = digitalindiasw.net

see the DB creation process and o/p

SQL> create database link oemrep.digitalindiasw.net
  2  connect to scott identified by tiger
  3  using 'OEMREP';

Database link created.

SQL> select * from tab@oemrep.digitalindiasw.net;
select * from tab@oemrep.digitalindiasw.net
                  *
ERROR at line 1:
ORA-12154: TNS:could not resolve service name


SQL> drop database link oemrep.digitalindiasw.net;

Database link dropped.

SQL> create database link oemrep.digitalindiasw.net
  2  connect to scott identified by tiger
  3  using 'OEMREP.DIGITALINDIASW.NET';

Database link created.

SQL> select * from tab@oemrep.digitalindiasw.net;

TNAME                          TABTYPE            CLUSTERID
------------------------------ ------- --------------------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEMP_EMP                       TABLE
TEST_DESC                      SYNONYM
T_EMPLOYEES                    TABLE
T_YTIVCR                       TABLE
YTIVCR                         TABLE
YTIVCRD                        TABLE

10 rows selected.

Now the SQL*NET is

# SQLNET.ORA Network Configuration File: C:\Oracle\Ora9i\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

--NAMES.DEFAULT_DOMAIN = world

NAMES.DEFAULT_DOMAIN = digitalindiasw.net

I restart my listener

Now see the DB creation process and o/p

SQL> drop database link oemrep;

Database link dropped.

SQL> create database link oemrep
  2  connect to scott identified by tiger
  3  using 'OEMREP';

Database link created.

SQL> select * from tab@oemrep;

TNAME                          TABTYPE            CLUSTERID
------------------------------ ------- --------------------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEMP_EMP                       TABLE
TEST_DESC                      SYNONYM
T_EMPLOYEES                    TABLE
T_YTIVCR                       TABLE
YTIVCR                         TABLE
YTIVCRD                        TABLE

10 rows selected.
SQL>

Its always better if u add the domain name to ur connect_string.
Alteast i would say to be on safer side.

Thanks
Abhay.

[adewri]

Originally posted by abhaysk
It would be necessary in a situation some thing like this.

Why do you need to compare your situation with others. You assume that your situation is the only situation that can occur. There can be more than one situations. What if i do not use sqlnet.ora file at all. What if i have sqlenet.ora and i do not use NAMES.DEFAULT_DOMAIN.

Originally posted by abhaysk

Its always better if u add the domain name to ur connect_string.
Alteast i would say to be on safer side.

Well if you want a safer side then remove the NAMES.DEFAULT_DOMAIN from your sqlnet.ora file. So you dont have to be dependent on those stupid domain naming conventions.

And you do not have to show me examples. I can understand what you want to say. And i said its not necessary it can be done any way you want to.

[adewri]

Originally posted by abhaysk

# SQLNET.ORA Network Configuration File: C:\Oracle\Ora9i\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = world

--NAMES.DEFAULT_DOMAIN = digitalindiasw.net

see the DB creation process and o/p

SQL> create database link oemrep.digitalindiasw.net
  2  connect to scott identified by tiger
  3  using 'OEMREP';

Database link created.

SQL> select * from tab@oemrep.digitalindiasw.net;
select * from tab@oemrep.digitalindiasw.net
                  *
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

And BTW if you use SID instead of SERVICE_NAME in your tnsnames.ora this will also work.

[abhaysk]

Originally posted by adewri
Why do you need to compare your situation with others. You assume that your situation is the only situation that can occur. There can be more than one situations.

Amar :

I didnt tell this is the only situation...but this(wat i showed) could be one of the situations.

Thinking that i tried to help him. Is that offence?

Well if you want a safer side then remove the NAMES.DEFAULT_DOMAIN from your sqlnet.ora file. So you dont have to be dependent on those stupid domain naming conventions.

Correct if TNS names have SID and not service_names to connect to DB.

And you do not have to show me examples. I can understand what you want to say. And i said its not necessary it can be done any way you want to.[

I didnt want to show u examples but i thought this wud giv him(thread starter) some idea where he might be going wrong.

Why are you taking it in wrong way, that i tried to explain u or tired to degrade u.

AFA "NOT NECESSARY" is conserned, it surely is in some cases ( had TNS names not using SID to connect ).

Abhay.

[abhaysk]

Originally posted by adewri
And BTW if you use SID instead of SERVICE_NAME in your tnsnames.ora this will also work.

Absolutely.

Now by such discussions starter will come to know where he is going wrong na.

[adewri]

Originally posted by abhaysk
Why are you taking it in wrong way, that i tried to explain u or tired to degrade u.

I think i'm not in good mood today, lots of pending work to be finished.