DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01042 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01042: Detaching a Session with Open Cursors Not Allowed

ORA-01042 is an Oracle error that occurs when an application attempts to detach a database session while one or more cursors are still open. This is most commonly seen in XA (distributed transaction) environments, connection pooling scenarios, and multi-threaded applications that fail to explicitly close cursors before releasing a connection. Left unaddressed, this error can cascade into ORA-01000 (maximum open cursors exceeded) and cause serious application outages.

Top 3 Causes

1. Unclosed Cursors in XA Distributed Transactions

In Java EE environments using XA datasources (WebLogic, JBoss, etc.), all cursors must be closed before calling xa_end() to detach the session. Missing a close() call in an exception handler is the most frequent root cause.

-- Check open cursors for a specific session
SELECT 
    oc.cursor#,
    oc.sql_text,
    oc.last_sql_active_time
FROM 
    v$open_cursor oc
JOIN 
    v$session s ON oc.saddr = s.saddr
WHERE 
    s.sid = :your_sid
ORDER BY 
    oc.last_sql_active_time DESC;
Enter fullscreen mode Exit fullscreen mode

2. Missing Cursor Cleanup in Application Code

When developers use PreparedStatement or ResultSet without a finally block or try-with-resources, cursors remain open on the Oracle side even after the Java object goes out of scope. The cursor is only released when Oracle's garbage collection kicks in — which may be too late.

-- PL/SQL: Always close cursors explicitly, even on exceptions
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary FROM employees WHERE rownum <= 100;
    v_rec c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_rec;
        EXIT WHEN c_emp%NOTFOUND;
        -- process record
    END LOOP;
    CLOSE c_emp;  -- explicit close before session detach
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        IF c_emp%ISOPEN THEN
            CLOSE c_emp;  -- close on error path too
        END IF;
        ROLLBACK;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Connection Pool Returning Dirty Connections

Connection pools like HikariCP, DBCP, or Oracle UCP internally detach the session when a connection is returned. If the connection has open cursors at that point, ORA-01042 is raised. Outdated JDBC drivers or misconfigured pool settings (missing resetOnReturn) are common culprits.

-- Detect sessions holding excessive open cursors
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    COUNT(oc.cursor#) AS open_cursors
FROM 
    v$session s
JOIN 
    v$open_cursor oc ON s.saddr = oc.saddr
GROUP BY 
    s.sid, s.serial#, s.username, s.program
HAVING 
    COUNT(oc.cursor#) > 20
ORDER BY 
    open_cursors DESC;

-- Kill a problem session if needed
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- 1. Check current OPEN_CURSORS parameter
SHOW PARAMETER open_cursors;

-- 2. Temporarily increase OPEN_CURSORS as a short-term relief
ALTER SYSTEM SET open_cursors = 500 SCOPE = BOTH;

-- 3. Monitor cursor usage trends
SELECT 
    name, 
    value
FROM 
    v$sysstat
WHERE 
    name IN (
        'opened cursors cumulative',
        'opened cursors current'
    );
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always use try-with-resources in Java — Wrap every Connection, PreparedStatement, and ResultSet in a try-with-resources block. This guarantees close() is called regardless of whether the code path exits normally or through an exception. For PL/SQL, always check %ISOPEN in the EXCEPTION block before calling CLOSE.

  2. Set up proactive cursor monitoring — Schedule a daily job using DBMS_SCHEDULER to query v$open_cursor and alert when any session exceeds 80% of the OPEN_CURSORS limit. Integrate this check into your OEM or custom monitoring dashboards to catch cursor leaks before they trigger an outage.

Related Errors

  • ORA-01000 — Maximum open cursors exceeded; the most common companion error to ORA-01042.
  • ORA-01001 — Invalid cursor; occurs when operating on an already-closed cursor.
  • ORA-02089 — COMMIT not allowed in a subordinate XA session; similar XA context error.

📖 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.

Top comments (0)