Oracle database links (DBLinks) provide a fundamental mechanism for enabling communication and data exchange between separate Oracle database instances. A DBLink defines a logical path from a local database to a remote databace, allowing users and applications to query tables, execute procedures, and perform other operations on the remote system as if they were local objects. This capability is crucial in distributed database architectures.
Core Concepts of Database Links
Database links are inherently unidirectional. When a DBLink is created on a source database, it stores connection details in its data dictionary. Upon activation, Oracle Net Services uses these stored credentials and network information to establish a session with the target remote database, facilitating the requested operasions. Think of a database link as a configured tunnel allowing a specific local database to initiate a connection to a specific remote database.
Prerequisites for DBLink Creation and Use:
- Network Connectivity: The local database server must be able to reach the remote database server over the network. This can typically be verified using
tnspingfrom the local server to the remote database's listener. For example,tnsping remote_db_service_name. - Remote Database Access: A valid username and password must exist on the remote database, possessing the necessary privileges to access the desired schemas and objects.
Types of Database Links
Oracle classifies database links into three primary types, each with a distinct scope and ownership:
| Type | Owner | Description |
|---|---|---|
| Private | The user who created the link | A private database link is associated with a specific schema. Only sessions connected to that schema can utilize this link to access remote databases. The creator is also the sole individual capable of dropping their private DBLink. |
| Public | PUBLIC |
A public database link is accessible to all users and PL/SQL programs within the local database that possess the necessary database access privileges. These links are database-level objects and offer broader utility. |
| Global | PUBLIC (managed by directory server) |
In environments leveraging an Oracle directory server, global database links are automatically managed as net service names for every Oracle Database in the network. Users and programs from any database can use these global links to interact with objects in the corresponding remote database. This type supersedes the older Oracle Names server-based global links. |
Creating Database Links
To create a database link, the user must have either the CREATE DATABASE LINK privilege (for private links) or the CREATE PUBLIC DATABASE LINK privilege (for public links). These privileges can be granted by an administrator:
GRANT CREATE DATABASE LINK TO app_user;
GRANT CREATE PUBLIC DATABASE LINK TO dba_team_role;
The syntax for creating a database link involves specifying its name, the remote user credentials, and the network connection string:
1. Private Database Link:
-- Using a TNS alias defined in tnsnames.ora
CREATE DATABASE LINK finance_data_link
CONNECT TO remote_finance_user IDENTIFIED BY