How do I create a Database Link where remote and local are the same server

Question

I have a need to create a database link that is a link to a schema on the same server. Is there a special keyword to use for this? ( like local or localhost )

I am not entirely sure what the name of the server is in tnsnames and that may be part of my problem.

This is for a complicated situation which involves a script that needs a database link to continue.

Answer 1

The DB link mechanism goes through TNS, so just define a TNS entry for your local database and use that in your link.

Your client TNSNAMES.ORA files should contain an entry that looks something like:

YourDBAlias =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = yourHOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = YourDB)
    )
  )

Make sure this entry also exists in the TNSNAMES.ORA file on your database server.

Then, create the database link as:

CREATE [PUBLIC] DATABASE LINK yourLinkName
       CONNECT TO theSchema IDENTIFIED BY thePW
       USING 'YourDBAlias';

This should do the trick (assuming you're using TNS naming).

Additionally, if you're not sure what your TNS Name is for the database, in SQL*Plus you can issue the command:

show parameter service_names

This will show you the name(s) that the database is registered with it's listener as. You should be able to find the corresponding entry in the TNSNAMES.ORA file from that.