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>;
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.