Database links
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Database links

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.


  2. #2
    Join Date
    Apr 2002
    Posts
    4
    May be a silly question,
    Do you have the tnsnames in the source machine pointing to the remote machine?

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  4. #4
    Join Date
    Apr 2002
    Posts
    4
    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.




  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    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

  7. #7
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    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

  8. #8
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    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

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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)))';

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    THANKS

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width