Accessing Remote Data
Database links enable a local Oracle database to connect to a remote Oracle database. They facilitate seamless querying and DML operations on data residing in other databases as if it were local, enhancing distributed data management. This powerful feature simplifies cross-database communication for developers and DBAs.
CREATE DATABASE LINK
The CREATE DATABASE LINK statement establishes a connection from a local database to a remote database. This allows users to access remote tables and views by simply appending @dblink_name to the object name. It's a cornerstone for distributed database architecture.
Example 1: CREATE DATABASE LINK (Fixed User)
CREATE DATABASE LINK remote_db_link -- Name of the database link
CONNECT TO remote_user IDENTIFIED BY "RemoteP@ssw0rd!" -- Credentials for the remote database user
USING 'remote_db_service'; -- TNS alias or connect string for the remote database
Explanation This code creates a database link named remote_db_link that connects to a remote database using the specified remote_user credentials and the remote_db_service TNS alias.
Example 2: CREATE DATABASE LINK (Current User)
CREATE DATABASE LINK current_user_link -- Link name for current user connection
CONNECT TO CURRENT_USER -- Uses the credentials of the user creating the link
USING 'remote_db_service_prod'; -- TNS alias for the production remote database
Explanation This statement creates a database link where the connection to the remote database is established using the username and password of the user currently executing the CREATE DATABASE LINK statement.
Example 3: CREATE DATABASE LINK (Public Link)
CREATE PUBLIC DATABASE LINK public_remote_link -- Creates a link accessible by all users
CONNECT TO remote_public_user IDENTIFIED BY "PublicP@ssw0rd!" -- User for public access
USING 'hr_app_db'; -- TNS alias for the HR application database
Explanation This example creates a PUBLIC database link, making it available to all users in the local database. It uses a dedicated remote_public_user for connections.
Example 4: CREATE DATABASE LINK (Non-default Port)
CREATE DATABASE LINK custom_port_link
CONNECT TO app_user IDENTIFIED BY "AppUserP@ss!"
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora19c_sid)))';
-- Direct connect string specifying host and custom port
Explanation This code demonstrates creating a database link using a direct TNS connect string, explicitly defining the IP address, a non-default port, and the service name for the remote database connection.
Example 5: CREATE DATABASE LINK (Drop Existing)
DROP DATABASE LINK existing_db_link; -- Removes an existing database link
CREATE DATABASE LINK existing_db_link
CONNECT TO new_user IDENTIFIED BY "NewP@ss!"
USING 'test_db_service'; -- Recreates the link with new credentials or TNS
Explanation This example first drops an existing database link and then recreates it, which is useful for updating connection details like usernames, passwords, or TNS aliases.
Querying Data Across Databases
Once a database link is established, you can query data from remote tables and views as if they were local. Simply append @dblink_name to the table or view name in your SQL queries. This capability is fundamental for distributed queries and integrating data from disparate systems.
Example 1: Basic SELECT from Remote Table
SELECT employee_id, first_name, last_name
FROM employees@remote_db_link -- Querying the 'employees' table via the database link
WHERE department_id = 10;
Explanation This query retrieves specific columns from the employees table located in the remote database, accessed through remote_db_link, filtering by department_id.
Example 2: Joining Local and Remote Tables
SELECT l.order_id, l.order_date, r.product_name
FROM local_orders l
JOIN products@remote_db_link r ON l.product_id = r.product_id -- Joining 'local_orders' with a remote 'products' table
WHERE l.order_date > SYSDATE - 30;
Explanation This SQL joins a local table local_orders with a remote table products (accessed via remote_db_link) to retrieve combined data, demonstrating cross-database joins.
Example 3: Performing DML on Remote Table (Caution)
INSERT INTO customers@remote_db_link (customer_id, customer_name)
VALUES (201, 'Global Corp'); -- Inserting data into a remote table
COMMIT; -- Committing the transaction across the database link
Explanation This example shows an INSERT operation on a remote customers table. While possible, DML over database links should be used with caution due to distributed transaction complexities.
Example 4: Calling Remote Function/Procedure
SELECT remote_function@remote_db_link('input_param') AS result_value
FROM DUAL; -- Calling a function on the remote database
Explanation This query demonstrates how to call a function (remote_function) that resides in the remote database through the database link and retrieve its result.
Example 5: Using Database Link in Subquery
SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees@remote_db_link WHERE salary > 100000);
-- Using a subquery that accesses a remote table
Explanation This example uses a subquery that retrieves department_id from a remote employees table to filter results in the local departments table, showcasing remote data integration.