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.

Answer 2

If you can't amend TNSNAMES.ORA you can use the Easy Connect syntax even for DB Links. Assuming the listener is on the default port, then the following SQL will get the conneection string

select utl_inaddr.get_host_address||':1521/'||sys_context('USERENV','INSTANCE_NAME') from dual

Answer 3

You may have a problem if by "schema on the same server" you mean a schema in the same database. (For example, if the script was treating anything outside of the schema as an external database to flatten subsequent SQL operations).

Oracle treats loopback links somewhat differently, and you may receive ORA-02082 errors ("a loopback database link must have a connection qualifier") if using the database global name for the link. If you receive this error, you have to name the link something different, like "loopback", but this also requires that the global_names database parameter is set to false. Otherwise you'll receive "ORA-02085: database link someName connects to someOtherName"