Database Links Analysis (ddblink.sql)

What This Script Does

This script displays database links configuration showing ownership, connection details, and target database information. It helps DBAs manage distributed database connections, troubleshoot connectivity issues, and audit database link usage across schemas.

Script

rem  ddblink.sql
rem
rem  linesize = 80
rem
ttitle 'Database Links'
rem
col owner format a12 heading 'OWNER'
col db_link format a15 heading 'DATABASE LINK'
col username format a12 heading 'USERNAME'
col password format a10 heading 'PASSWORD'
col host format a17 heading 'HOST'
col created format a9 heading 'CREATED'
rem
break on owner skip 1
rem
select owner, db_link, username, host, created
  from sys.dba_db_links
 where owner like upper('&owner')
 order by owner, db_link;

Usage

-- Run the script in SQL*Plus or SQLcl
@ddblink.sql

-- When prompted, enter:
-- owner: Schema owner pattern (% for all)

-- Examples
Enter value for owner: %     -- Show all database links
Enter value for owner: HR    -- Show links owned by HR schema

Parameters

&owner: Schema owner pattern (use % for all schemas)

Required Privileges

SELECT on SYS.DBA_DB_LINKS
Typically requires DBA role

Sample Output

Database Links

OWNER        DATABASE LINK   USERNAME     HOST             CREATED
------------ --------------- ------------ ---------------- ---------
HR           REMOTE_HR       HR_USER      remote-db.com    15-MAR-24
             BACKUP_DB       BACKUP_USER  backup-srv.com   20-MAR-24

PUBLIC       SHARED_LINK     APP_USER     shared-db.com    01-JAN-24
             REPORTING       REPORT_USER  report-db.com    15-FEB-24

SALES        SALES_DW        DW_USER      warehouse.com    10-APR-24

Key Output Columns

OWNER: Schema owner of the database link
DATABASE LINK: Name of the database link
USERNAME: Username for remote database connection
HOST: Target database host or connect string
CREATED: Date when the link was created

Understanding Database Links

Database Link Types

Private Links: Owned by specific schema (HR, SALES, etc.)
Public Links: Available to all users (OWNER = ‘PUBLIC’)
Global Links: Managed by Oracle Names or LDAP

Security Considerations

Fixed User Links: Use specified username/password
Connected User Links: Use current session credentials
Shared Links: Multiple users share same connection

Performance Analysis

Link Usage Monitoring

-- Check active database link sessions
SELECT db_link, in_transaction,
       opened, logged_on,
       heterogeneous, protocol
FROM v$dblink;

Connection Pool Analysis

-- Monitor database link performance
SELECT owner, db_link_name,
       open_connections,
       in_transaction
FROM dba_db_link_sources
WHERE open_connections > 0;

Common Use Cases

Database Link Inventory

-- List all database links in the system
@ddblink.sql
-- Enter % for owner
-- Review all configured links

Security Audit

-- Check for shared or public links
@ddblink.sql
-- Look for PUBLIC owner
-- Review username patterns

Troubleshooting Connectivity

-- Identify problematic links
@ddblink.sql
-- Test connectivity for each link
-- Check host reachability

Troubleshooting Database Links

Test Link Connectivity

-- Test database link functionality
SELECT COUNT(*) FROM dual@REMOTE_HR;

-- Check remote database version
SELECT banner FROM v$version@REMOTE_HR WHERE rownum = 1;

Connection Issues

-- Verify link configuration
SELECT owner, db_link, username, host
FROM dba_db_links
WHERE db_link = 'REMOTE_HR';

-- Check TNS configuration
SELECT db_link, host
FROM dba_db_links
WHERE host LIKE '%tns_name%';

Performance Problems

-- Monitor link response time
SELECT db_link,
       COUNT(*) connections,
       AVG(open_cursors) avg_cursors
FROM v$dblink
GROUP BY db_link;

Advanced Analysis

Link Dependencies

-- Find objects using database links
SELECT owner, name, type, referenced_owner, referenced_name
FROM dba_dependencies
WHERE referenced_link_name IS NOT NULL
ORDER BY owner, name;

Cross-Database Queries

-- Identify views using database links
SELECT owner, view_name, text
FROM dba_views
WHERE text LIKE '%@%'
ORDER BY owner, view_name;

Security Analysis

-- Check for links with stored passwords
SELECT owner, db_link,
       CASE WHEN username IS NOT NULL THEN 'Fixed User'
            ELSE 'Connected User' END link_type,
       host
FROM dba_db_links
ORDER BY link_type, owner;

Link Usage Statistics

-- Historical link usage (if auditing enabled)
SELECT db_link,
       COUNT(*) access_count,
       MIN(timestamp) first_access,
       MAX(timestamp) last_access
FROM dba_audit_trail
WHERE obj_name LIKE '%@%'
GROUP BY db_link
ORDER BY access_count DESC;

Database Link Management

Creating Database Links

-- Create private database link
CREATE DATABASE LINK remote_hr
CONNECT TO hr_user IDENTIFIED BY password
USING 'remote_db_tns';

-- Create public database link
CREATE PUBLIC DATABASE LINK shared_link
CONNECT TO app_user IDENTIFIED BY password
USING 'shared_db_tns';

-- Create connected user link
CREATE DATABASE LINK current_user_link
CONNECT TO CURRENT_USER
USING 'target_db_tns';

Maintenance Operations

-- Drop database link
DROP DATABASE LINK remote_hr;

-- Drop public database link
DROP PUBLIC DATABASE LINK shared_link;

Security Best Practices

Password Management

Avoid storing passwords in database links when possible
Use connected user links with proper authentication
Regularly rotate passwords for fixed user links
Consider using Oracle Wallet for credential storage

Access Control

-- Grant database link usage
GRANT CREATE DATABASE LINK TO schema_owner;

-- Revoke database link privileges
REVOKE CREATE DATABASE LINK FROM schema_owner;

Monitoring

-- Regular security audit
SELECT owner, db_link, username,
       CASE WHEN username IS NULL THEN 'SECURE'
            ELSE 'REVIEW' END security_status
FROM dba_db_links
ORDER BY security_status DESC, owner;

Database Information (../database-info/db.sql) - Database overview
User Analysis (../security-analysis/duser.sql) - User account management
Network Configuration (../administration/vcontrol.sql) - Database configuration
Dependencies Analysis (dobject.sql) - Object dependencies