ORA-02020: Too Many Database Links in Use - OPEN_LINKS Tuning

ORA-02020: Too Many Database Links in Use

Error Overview

Error Text: ORA-02020: too many database links in use

ORA-02020 occurs when a session attempts to use more concurrent database links than allowed by the OPEN_LINKS initialization parameter. Each session has a limit on how many remote connections can be open simultaneously, and exceeding this limit triggers the error.

Understanding OPEN_LINKS Parameters

Parameter	Default	Purpose
OPEN_LINKS	4	Max concurrent DB links per session
OPEN_LINKS_PER_INSTANCE	4	Max concurrent links across instance for migratable transactions

Common Causes

Too Low OPEN_LINKS Setting

• Default value of 4 too low for distributed application
• Application accesses many remote databases in single session
• Reporting queries spanning multiple remote sources

Links Not Being Closed

• Application not calling ALTER SESSION CLOSE DATABASE LINK
• Session reuses connection pool entries with open links
• Long-running PL/SQL keeping links open across iterations

Cursor Holding Behavior

• Cursors over remote tables hold link open until closed
• FOR UPDATE cursors over @dblink tables
• Implicit cursors in PL/SQL not finalizing

Distributed Transaction Sprawl

• Two-phase commit involving many remote DBs
• Application architecture requiring many distributed sources

Diagnostic Steps

Check Current Parameter Values

-- View current OPEN_LINKS setting
SHOW PARAMETER open_links;

-- Or via v$parameter
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
WHERE name LIKE 'open_links%';

Identify Active Database Links

-- Show open links in current session
SELECT * FROM v$dblink;

-- All open distributed transactions
SELECT inst_id, sid, serial#, oct, schemaname,
       db_link, in_transaction
FROM gv$dblink;

-- Sessions with high DB link usage
SELECT s.sid, s.serial#, s.username, COUNT(*) AS link_count
FROM v$session s, v$dblink d
WHERE s.sid = USERENV('SID')
GROUP BY s.sid, s.serial#, s.username;

Check for Hanging Distributed Transactions

-- 2PC pending transactions
SELECT local_tran_id, global_tran_id, state, mixed,
       fail_time, retry_time
FROM dba_2pc_pending;

-- Distributed transaction details
SELECT * FROM dba_2pc_neighbors;

Resolution Steps

1. Increase OPEN_LINKS Parameter

-- Modify OPEN_LINKS (requires restart)
ALTER SYSTEM SET open_links = 20 SCOPE=SPFILE;
ALTER SYSTEM SET open_links_per_instance = 20 SCOPE=SPFILE;

-- Restart database
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify
SHOW PARAMETER open_links;

2. Close Links Explicitly

-- Close specific link in session
ALTER SESSION CLOSE DATABASE LINK target_db;

-- Close after each use in PL/SQL
DECLARE
    CURSOR c1 IS SELECT * FROM remote_table@target_db;
BEGIN
    FOR r IN c1 LOOP
        -- process row
        NULL;
    END LOOP;

    EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK target_db';
EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK target_db';
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
        RAISE;
END;
/

Note: A link cannot be closed while there is an active transaction or open cursor using it. COMMIT first, then close.

3. Audit and Refactor Distributed Code

-- Find all remote queries in stored code
SELECT name, type, line, text
FROM dba_source
WHERE UPPER(text) LIKE '%@%'
ORDER BY name, line;

-- Convert serial remote calls into single MV refresh
CREATE MATERIALIZED VIEW remote_summary
REFRESH COMPLETE ON DEMAND
AS SELECT * FROM remote_table@target_db;

-- Then query MV instead of remote table repeatedly

4. Use Connection Pooling Intelligently

-- Set link to close at end of session
ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS = TRUE;

-- For application connection pools, close links before returning to pool
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK link_a';
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK link_b';

5. Cleanup Hung 2PC Transactions

-- Identify pending
SELECT local_tran_id, state FROM dba_2pc_pending;

-- Force commit (only if confirmed remote committed)
COMMIT FORCE 'local_tran_id';

-- Force rollback (only if confirmed remote rolled back)
ROLLBACK FORCE 'local_tran_id';

-- Clean up records
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

Common Scenarios

Scenario 1: Reporting Tool Hits Limit

Application queries 6 remote DBs in one session, OPEN_LINKS=4.
ORA-02020: too many database links in use

Fix: Increase to OPEN_LINKS=10 and OPEN_LINKS_PER_INSTANCE=20.

Scenario 2: PL/SQL Loop Leaking Links

Procedure loops through 100 customers, each query against @customer_db.
After 4 iterations, ORA-02020.

Fix: Close link after each iteration or restructure to single batch query.

Scenario 3: ETL with Many Sources

ETL pulls from 12 source databases via DB links.
ORA-02020: too many database links in use

Fix: Increase OPEN_LINKS to 15+, use materialized views to reduce concurrency.

Sample Output

SQL> SELECT * FROM remote_table_5@db_link_5;
SELECT * FROM remote_table_5@db_link_5
                              *
ERROR at line 1:
ORA-02020: too many database links in use

SQL> SELECT * FROM v$dblink;

DB_LINK         OWNER_ID  LOGGED_ON  HETEROGENEOUS  PROTOCOL
--------------- --------- ---------- -------------- --------
DB_LINK_1.WORLD       104 YES        NO             UNKN
DB_LINK_2.WORLD       104 YES        NO             UNKN
DB_LINK_3.WORLD       104 YES        NO             UNKN
DB_LINK_4.WORLD       104 YES        NO             UNKN

-- 4 links open, OPEN_LINKS=4 reached
SQL> SHOW PARAMETER open_links;
NAME            TYPE        VALUE
--------------- ----------- ------
open_links      integer     4

-- Close one and retry
SQL> COMMIT;
SQL> ALTER SESSION CLOSE DATABASE LINK db_link_1;
SQL> SELECT * FROM remote_table_5@db_link_5;  -- now succeeds

Prevention Strategies

Right-Size OPEN_LINKS at Setup

-- Calculate based on application architecture
-- # remote DBs simultaneously needed × concurrent sessions
ALTER SYSTEM SET open_links = 15 SCOPE=SPFILE;
ALTER SYSTEM SET open_links_per_instance = 30 SCOPE=SPFILE;

Use Materialized Views for Repeated Access

-- Pull data locally once
CREATE MATERIALIZED VIEW mv_remote_customers
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS SELECT * FROM customers@remote_db;

-- Application queries MV, not remote
SELECT * FROM mv_remote_customers WHERE region = 'WEST';

Application Design Patterns

• Aggregate remote queries into a single fetch
• Cache remote data in local tables for repeated access
• Close links explicitly when done
• Monitor v$dblink in long-running sessions

Monitoring Script

-- Alert if any session approaches limit
SELECT s.sid, s.username, COUNT(*) AS link_count
FROM gv$session s, gv$dblink d
WHERE s.sid = d.sid AND s.inst_id = d.inst_id
GROUP BY s.sid, s.username
HAVING COUNT(*) >= (SELECT TO_NUMBER(value) - 1
                    FROM v$parameter
                    WHERE name = 'open_links');

Related Errors

• ORA-02011: Duplicate database link name
• ORA-02019: Connection description for remote database not found
• ORA-02050: Transaction X rolled back, some remote DBs may be in-doubt
• ORA-02063: Preceding line from database link
• ORA-02068: Following severe error from database link

Troubleshooting Checklist

• Check current OPEN_LINKS and OPEN_LINKS_PER_INSTANCE
• Inspect v$dblink for active connections
• Close idle links with ALTER SESSION CLOSE DATABASE LINK
• Increase parameter if architecture demands it
• Audit code for unclosed links in loops
• Use materialized views for frequently-accessed remote data
• Clean up dba_2pc_pending entries