Oracle Error ORA-01000

Error code

ORA-01000

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Space, Memory & Resource Limits

Last reviewed

24 October 2026

What it means

ORA-01000 means one database session tried to have more cursors open at the same time than the OPEN_CURSORS initialization parameter allows, usually because the application opens statements or result sets and does not close them.

Every SQL statement a session runs uses a cursor, and OPEN_CURSORS (default 50 in the 19c Database Reference) caps how many a single session may hold open at once. Oracle's current reference names two root causes: the workload genuinely needs more concurrently open cursors than the limit, or the application has a cursor leak, opening cursors and never closing them, or closing them later than needed. A leak makes the count climb steadily until the session fails, often hours after a connection pool handed it out.

In current releases the message template ends with an extra detail field, and Oracle's Action text says the error text may identify a potentially leaked SQL_ID for a developer to review; the exact content of that field is not documented, so this page shows the message without it. 19c and 21c print "maximum open cursors exceeded". Raising the limit stops the error only when the workload really needs more cursors; with a leak it just delays it.

Common causes

• Application code (JDBC, ODP.NET, Python and so on) creates statements or result sets in a loop and does not close them, especially on error paths.
• A PL/SQL function returns a SYS_REFCURSOR to the client, and the client never closes the result set.
• Dynamic SQL with DBMS_SQL.OPEN_CURSOR runs without a matching DBMS_SQL.CLOSE_CURSOR.
• A client-side statement cache is set larger than OPEN_CURSORS allows, so cached statements alone fill the session's quota.
• The workload legitimately needs many open cursors per session (complex batch jobs, many prepared statements) and OPEN_CURSORS is too low for it.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT name, value
FROM v$parameter
WHERE name IN ('open_cursors', 'session_cached_cursors');

SELECT s.sid, s.serial#, s.username, s.program, ss.value AS open_cursors
FROM v$sesstat ss
JOIN v$statname sn ON sn.statistic# = ss.statistic#
JOIN v$session s   ON s.sid = ss.sid
WHERE sn.name = 'opened cursors current'
  AND s.username IS NOT NULL
ORDER BY ss.value DESC
FETCH FIRST 20 ROWS ONLY;

SELECT sid, user_name, cursor_type, sql_id, sql_text, COUNT(*) AS copies
FROM v$open_cursor
WHERE user_name = 'SHOP_APP'
GROUP BY sid, user_name, cursor_type, sql_id, sql_text
ORDER BY copies DESC
FETCH FIRST 20 ROWS ONLY;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE OR REPLACE PROCEDURE shop.run_dynamic (p_sql IN VARCHAR2) IS
    c    INTEGER := DBMS_SQL.OPEN_CURSOR;
    rows INTEGER;
BEGIN
    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
    rows := DBMS_SQL.EXECUTE(c);
    DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(c) THEN
            DBMS_SQL.CLOSE_CURSOR(c);
        END IF;
        RAISE;
END;
/

ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH;

Examples

Dynamic SQL that never closes its cursor

Not reproduced here: no Oracle Database is installed for this site. The first line follows Oracle's current error reference without the trailing detail field, whose content is not documented; the ORA-06512 line and its line number are illustrative, and the full stack may include lines from SYS.DBMS_SQL. Oracle Database 19c and 21c print "ORA-01000: maximum open cursors exceeded".

-- open_cursors = 300
DECLARE
    c INTEGER;
BEGIN
    FOR i IN 1 .. 1000 LOOP
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'SELECT 1 FROM dual', DBMS_SQL.NATIVE);
        -- missing: DBMS_SQL.CLOSE_CURSOR(c);
    END LOOP;
END;
/

ORA-01000: maximum open cursors for session exceeded
ORA-06512: at line 6

Ref cursors returned to an application that never closes them

Not reproduced here: no Oracle Database is installed for this site. The message format comes from Oracle's error reference (shown without the current releases' trailing detail field). The error appears on whichever call reaches the limit, often far from the code that leaks; V$OPEN_CURSOR shows the SELECT from orders many times for that session.

CREATE OR REPLACE FUNCTION get_customer_orders (p_customer_id IN NUMBER)
    RETURN SYS_REFCURSOR
IS
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT order_id, amount FROM orders WHERE customer_id = p_customer_id;
    RETURN rc;
END;
/
-- the application calls this once per customer on one pooled connection
-- and reads each result set, but never closes it

ORA-01000: maximum open cursors for session exceeded

How to prevent it

Make closing automatic: try-with-resources, using blocks or context managers for every statement and result set, cursor FOR loops in PL/SQL, and an exception handler that closes any DBMS_SQL cursor. Review code paths that return early or throw.

Monitor the "opened cursors current" statistic per session (Oracle's reference gives a query for it) to learn how many cursors normal operation needs, and size OPEN_CURSORS and client statement caches from that, with headroom. A count that only ever rises is a leak.

Common questions

Official documentation

• ORA-01000 (Oracle Database Error Messages)
• OPEN_CURSORS (Oracle Database Reference 19c)
• V$OPEN_CURSOR (Oracle Database Reference 19c)
• SESSION_CACHED_CURSORS (Oracle Database Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.