Database link

A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another.

The following link types are supported:

• Private database link - belongs to a specific schema of a database. Only the owner of a private database link can use it.
• Public database link - all users in the database can use it.
• Global database link - defined in an OID or Oracle Names Server. Anyone on the network can use it.

Create and drop

Create a DBlink:

CREATE DATABASE LINK remotedb CONNECT TO scott IDENTIFIED BY tiger USING 'tns_conn_str';

Drop a DBlink:

DROP DATABASE LINK remotedb;

Close Database Link

To close a DBlink:

ALTER SESSION CLOSE DATABASE LINK <link_name>;

Drop a database link when you are not the owner

When you are owner of the link you can drop it without problem but if you are a DBA and want to purge database, let's say after restoring test database from live database, you can use the proxy user feature so you can connect to the database link owner without knowing or changing its password (below MICHEL is the DBA).

SQL> CONNECT test/test
Connected.
TEST> CREATE DATABASE LINK mika@loop CONNECT TO test IDENTIFIED BY test USING 'mika';

Database link created.

TEST> CONNECT michel/michel
Connected. 
MICHEL> ALTER USER test GRANT CONNECT THROUGH michel ;

User altered.

MICHEL> -- Connect to TEST through MICHEL account and so with MICHEL's password
MICHEL> CONNECT michel[test]/michel
Connected.
TEST> SHOW USER
USER is "TEST" 
TEST> DROP DATABASE LINK mika@loop;

Database link dropped.

TEST> CONNECT michel/michel
Connected. 
MICHEL> ALTER USER test REVOKE CONNECT THROUGH michel ;

User altered.