Oracle ORA-02020 Error: Causes and Solutions Complete Guide

ORA-02020: Too Many Database Links in Use

ORA-02020 occurs when a single Oracle session attempts to open more concurrent database links than the value configured in the OPEN_LINKS initialization parameter. The default value for OPEN_LINKS is 4, which can be easily exceeded in distributed query environments, complex ETL pipelines, or applications that connect to multiple remote databases simultaneously. When this limit is hit, Oracle immediately aborts the operation, potentially rolling back the entire transaction.

---

Top 3 Causes

1. OPEN_LINKS Parameter Set Too Low

The most common root cause is simply that the default value of OPEN_LINKS = 4 has never been reviewed or adjusted for the actual workload.

-- Check current OPEN_LINKS setting
SHOW PARAMETER OPEN_LINKS;

-- Check how many DB links are currently open in the session
SELECT DB_LINK, LOGGED_ON, OPEN_CURSORS, IN_TRANSACTION
FROM V$DBLINK;

-- Fix: Increase OPEN_LINKS (requires DB restart)
ALTER SYSTEM SET OPEN_LINKS = 10 SCOPE = SPFILE;

-- Also check instance-wide limit
ALTER SYSTEM SET OPEN_LINKS_PER_INSTANCE = 64 SCOPE = SPFILE;

Note: The maximum allowed value is 255. Restart the database after changing OPEN_LINKS via SPFILE.

---

2. Database Links Not Explicitly Closed After Use

When application code or PL/SQL procedures fail to close database links after use, those links remain open for the lifetime of the session. In connection pool environments, this accumulates rapidly.

-- BAD PATTERN: Link left open after use
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM orders@remote_db;
    -- Link stays open indefinitely!
END;
/

-- GOOD PATTERN: Explicitly close the link after use
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM orders@remote_db;
    DBMS_OUTPUT.PUT_LINE('Order count: ' || v_count);

    -- Always close the link when done
    EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK remote_db';
EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK remote_db';
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
        RAISE;
END;
/

---

3. Multiple DB Links Used Simultaneously in a Single Query

Joining across more than OPEN_LINKS remote databases in a single SQL statement will instantly trigger ORA-02020.

-- PROBLEMATIC: 5 DB links in one query (fails with OPEN_LINKS=4)
SELECT a.emp_id, b.dept_name, c.city, d.country, e.region
FROM employees@db1 a
JOIN departments@db2 b ON a.dept_id = b.dept_id
JOIN locations@db3 c ON b.loc_id = c.loc_id
JOIN countries@db4 d ON c.country_id = d.country_id
JOIN regions@db5 e ON d.region_id = e.region_id;

-- SOLUTION: Stage remote data into local temp tables first
CREATE GLOBAL TEMPORARY TABLE gtt_emp
    (emp_id NUMBER, dept_id NUMBER)
ON COMMIT DELETE ROWS;

-- Load one remote source at a time, then close the link
INSERT INTO gtt_emp SELECT emp_id, dept_id FROM employees@db1;
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK db1';

-- Now join locally — no DB links needed
SELECT e.emp_id FROM gtt_emp e WHERE e.dept_id = 10;

---

Quick Fix Solutions

-- 1. Identify sessions with high DB link usage
SELECT s.sid, s.username, s.program, COUNT(*) AS open_links
FROM v$session s
JOIN v$dblink d ON s.saddr = d.saddr
GROUP BY s.sid, s.username, s.program
ORDER BY open_links DESC;

-- 2. Close a specific DB link in the current session
ALTER SESSION CLOSE DATABASE LINK your_db_link_name;

-- 3. Verify the fix after parameter change and restart
SHOW PARAMETER OPEN_LINKS;
SELECT * FROM V$DBLINK;

---

Prevention Tips

1. Enforce a "close after use" coding standard for all DB link access. Add it to your team's code review checklist and create reusable PL/SQL wrapper procedures that automatically close links in the exception handler. Schedule a weekly query against V$DBLINK to catch leaked open links before they cause production issues.

2. Design distributed architectures to minimize DB link dependency. Where possible, replace DB links with Oracle Advanced Queuing (AQ), database replication, or REST-based integration. If DB links are unavoidable, document the maximum number of simultaneous links your application requires and set OPEN_LINKS proactively — not reactively after an incident. A good rule of thumb is to set OPEN_LINKS to at least 2x the maximum number of concurrent remote databases your heaviest session will ever need.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.