Oracle DB Links

Oracle DB Links

Query
To Query Existing DB Links:
SELECT * FROM USER_DB_LINKS

Or select from ALL_DB_LINKS, DBA_DB_LINKS

To query from DBA_DB_LINKS, user got to have SELECT ANY DICTIONARY Privilege.

Create
To Create a new DB Link
CREATE DATABASE LINK MY_LINK CONNECT TO my_user IDENTIFIED BY my_password USING 'my_connection_string'

Where my_connection_string is the entry from TNSNAMES.

By default Oracle would perform UPPER on user name and user password, so if you query USER_DB_LINKS, these fields would be in upper case.
To keep user_name and user_password in lower case, put them inside "". for example:
CREATE DATABASE LINK MY_LINK CONNECT TO "my_user" IDENTIFIED BY "my_password" USING 'my_connection_string'

Drop
To Drop existing DB Link
DROP DATABASE LINK MY_LINK 

Privilege
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. 
Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Key sensitive password
By Default Oracle would do UPPER to username and password.
For Key Sensitive password Oracle versions, i.e. Oracle 11G and up, put the password between "", so it would escape the UPPER function.


ORA-12154: TNS: could not resolve the connect identifier specified
Normally when TNS Error ORA-12154 occurs, it is related to the Oracle Client not able to resolve the given identifier in tnsnames.ora on the Client side.

However when receiving this error from a DB Link connection, it is a server side error, because the name resolution occurs on the server.

Check sqlnet.ora and tnsnames.ora on the Oracle Server.

Determining Which Link Connections Are Open
You may find it useful to determine which database link connections are currently open in your session.
Note that if you connect as SYSDBA, you cannot query a view to determine all the links open for all sessions; You can only access the link information in the session within which you are working.

The following views show the database link connections that are currently open in your current session:

SELECT * FROM V$DBLINK