What database links are
A database link is a schema object that defines a connection from a local Oracle database to a remote Oracle database. Once created, you access the remote database’s objects by appending @link_name to the table or view name.
Creating a database link
CREATE DATABASE LINK hcm_prod
CONNECT TO hcm_reader IDENTIFIED BY "password"
USING 'hcm_prod_tns';
Where hcm_prod_tns is a TNS alias defined in tnsnames.ora or an Easy Connect string.
For public links (accessible to all users):
CREATE PUBLIC DATABASE LINK hcm_prod ...;
Querying remote tables
-- Simple query:
SELECT * FROM employees@hcm_prod WHERE department_id = 20;
-- Join local and remote:
SELECT l.order_id, r.customer_name
FROM local_orders l
JOIN customers@crm_system r ON l.customer_id = r.id;
Distributed transactions (INSERT/UPDATE across links)
INSERT INTO staging_orders SELECT * FROM orders@ebs_legacy WHERE status = 'PENDING';
COMMIT; -- Oracle's two-phase commit ensures consistency
Stored procedures on remote databases
BEGIN
pkg_remote.process_order@finance_db(p_order_id => 1234);
END;
Performance considerations
Network latency makes every remote row access slower than local. For large data transfers, always:
- Filter aggressively on the remote side (push predicates through the link)
- Avoid bringing large result sets across the link then filtering locally
- Consider replication (materialized views) for frequently accessed remote data
Verify predicate pushdown is happening in the execution plan — look for REMOTE operations with sensible filtered row counts.
EBS-to-Cloud migration use case
During phased migrations where some modules are still in EBS and others have moved to Fusion Cloud, database links provide the bridge. An OIC DB adapter connection to the Oracle DB can use existing links transparently, or OIC can manage separate connections to each system.
Monitoring active links
SELECT * FROM v$dblink; -- active links in current session
Always test link availability before batch jobs that depend on it — don’t let a broken link cause a 3am silent failure.