Skip to content
Oracle DBA Scripts & Database Utilities

OPEN_CURSORS - Prevent ORA-01000 Maximum Open Cursors Exceeded

OPEN_CURSORS

Section titled “OPEN_CURSORS”

Overview

Section titled “Overview”

OPEN_CURSORS specifies the maximum number of open cursors (context areas) a single session can have at one time. Each SQL statement or PL/SQL block that a session opens consumes one cursor slot, and if a session attempts to open more cursors than this limit allows, Oracle raises ORA-01000. This parameter exists primarily to prevent runaway applications or cursor leaks from consuming unbounded memory in the shared pool.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 50 Valid Range: 1 to 65535 Available Since: Oracle 7 Modifiable: Yes — ALTER SYSTEM (takes effect for new sessions immediately; existing sessions keep their limit until reconnect) PDB Modifiable: Yes

Configuration

Section titled “Configuration”

Viewing Current Value

Section titled “Viewing Current Value”
-- Check current value from the parameter file (spfile)
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'open_cursors';


-- Check what is actually in the SPFILE (may differ from running value)
SELECT name, value
FROM   v$spparameter
WHERE  name = 'open_cursors';


-- Compare memory vs spfile values side by side
SELECT p.name,
       p.value        AS memory_value,
       sp.value       AS spfile_value,
       p.description
FROM   v$parameter   p
JOIN   v$spparameter sp ON sp.name = p.name
WHERE  p.name = 'open_cursors';

Setting the Parameter

Section titled “Setting the Parameter”
-- Set for current running instance only (lost on restart)
ALTER SYSTEM SET open_cursors = 300 SCOPE = MEMORY;


-- Persist to SPFILE (takes effect for new sessions; requires bounce for all sessions)
ALTER SYSTEM SET open_cursors = 300 SCOPE = SPFILE;


-- Set in both memory and SPFILE (recommended)
ALTER SYSTEM SET open_cursors = 300 SCOPE = BOTH;


-- Verify the change
SELECT name, value FROM v$parameter WHERE name = 'open_cursors';

Note: OPEN_CURSORS cannot be set at the session level with ALTER SESSION. It is a system-wide parameter only.

Tuning Guidance

Section titled “Tuning Guidance”
Section titled “Recommended Values”
EnvironmentTypical ValueRationale
Small OLTP (few users)300Safe baseline above the default of 50
Medium OLTP300–500Covers most well-written applications
Large OLTP / connection pool500–1000High concurrency, many prepared statements
PL/SQL-heavy batch systems500–2000Batch jobs open many cursors per session
Data warehouse / reporting300–500Typically fewer simultaneous open cursors

The default of 50 is almost always too low for production applications. A value of 300 is a common production starting point. Values above 1000 warrant investigation into whether the application is leaking cursors rather than simply requiring a higher limit.

How to Size

Section titled “How to Size”

The authoritative sizing source is V$OPEN_CURSOR, which tracks cursors currently open across all active sessions.

-- Maximum open cursors per session right now
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       COUNT(oc.cursor#)        AS open_cursors,
       s.status
FROM   v$session    s
JOIN   v$open_cursor oc ON oc.sid = s.sid
WHERE  s.username IS NOT NULL
GROUP  BY s.sid, s.serial#, s.username, s.program, s.status
ORDER  BY open_cursors DESC
FETCH FIRST 20 ROWS ONLY;


-- Peak open cursors recorded since instance startup
SELECT MAX(a.value) AS peak_open_cursors
FROM   v$sesstat  a
JOIN   v$statname b ON b.statistic# = a.statistic#
WHERE  b.name = 'opened cursors current';


-- Sessions approaching the OPEN_CURSORS limit (within 80%)
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       COUNT(oc.cursor#)                      AS open_cursors,
       (SELECT TO_NUMBER(value)
        FROM   v$parameter
        WHERE  name = 'open_cursors')          AS limit,
       ROUND(COUNT(oc.cursor#) /
             (SELECT TO_NUMBER(value)
              FROM   v$parameter
              WHERE  name = 'open_cursors') * 100, 1) AS pct_used
FROM   v$session     s
JOIN   v$open_cursor oc ON oc.sid = s.sid
WHERE  s.username IS NOT NULL
GROUP  BY s.sid, s.serial#, s.username, s.program
HAVING COUNT(oc.cursor#) > (SELECT TO_NUMBER(value) * 0.8
                             FROM   v$parameter
                             WHERE  name = 'open_cursors')
ORDER  BY open_cursors DESC;

Set OPEN_CURSORS to at least 120% of the highest observed peak from V$OPEN_CURSOR. Never set it so high that it becomes a ceiling for unbounded cursor leaks — if a session is consistently opening thousands of cursors, investigate the application.

Monitoring

Section titled “Monitoring”
-- Cumulative cursor statistics since instance startup
SELECT name, value
FROM   v$sysstat
WHERE  name IN (
    'opened cursors cumulative',
    'opened cursors current',
    'cursor authentications'
)
ORDER  BY name;


-- Per-session cursor counts with SQL text of open cursors
-- Useful for identifying which SQL is keeping cursors open
SELECT oc.sid,
       s.username,
       s.program,
       oc.cursor#,
       oc.sql_id,
       SUBSTR(sq.sql_text, 1, 80) AS sql_text
FROM   v$open_cursor oc
JOIN   v$session     s  ON s.sid = oc.sid
LEFT   JOIN v$sql    sq ON sq.sql_id = oc.sql_id
WHERE  s.username IS NOT NULL
ORDER  BY oc.sid, oc.cursor#;


-- Detect cursor leak candidates: sessions with growing cursor counts
-- Run this twice a few minutes apart and compare
SELECT sid, username, program, COUNT(*) AS open_cursor_count
FROM   v$open_cursor
JOIN   v$session USING (sid)
WHERE  username IS NOT NULL
GROUP  BY sid, username, program
ORDER  BY open_cursor_count DESC;

Common Issues

Section titled “Common Issues”

Issue 1: ORA-01000 After Application Deployment

Section titled “Issue 1: ORA-01000 After Application Deployment”

Symptom: Applications start receiving ORA-01000 errors after a new release, but the database previously ran without issues.

Cause: The new application version opens more cursors per session — often due to a new ORM framework, connection pooling changes, or a missing cursor.close() call.

Resolution:

  1. Identify which session and program is hitting the limit using the queries above.
  2. As an immediate fix, raise OPEN_CURSORS with ALTER SYSTEM SET open_cursors = 500 SCOPE = BOTH.
  3. Engage the development team to audit cursor lifecycle management in the new code. ORM frameworks such as Hibernate with Oracle JDBC should use Statement.close() or try-with-resources to ensure cursors are released.
  4. Consider tuning SESSION_CACHED_CURSORS in conjunction to reduce unnecessary re-opening of frequently used cursors.

Issue 2: OPEN_CURSORS Set Very High but ORA-01000 Still Occurs

Section titled “Issue 2: OPEN_CURSORS Set Very High but ORA-01000 Still Occurs”

Symptom: OPEN_CURSORS is set to 1000 or higher, yet specific sessions still hit ORA-01000.

Cause: The application has a genuine cursor leak — cursors are opened inside loops or error paths that never close them. Eventually any finite limit is exceeded.

Resolution:

-- Find the specific SQL statements with the most open cursors in leaking sessions
SELECT oc.sid,
       oc.sql_id,
       COUNT(*) AS cursor_count,
       MAX(SUBSTR(sq.sql_text, 1, 120)) AS sql_text
FROM   v$open_cursor oc
LEFT   JOIN v$sql sq ON sq.sql_id = oc.sql_id
WHERE  oc.sid = :target_sid   -- replace with leaking session SID
GROUP  BY oc.sid, oc.sql_id
ORDER  BY cursor_count DESC;

The same SQL ID appearing hundreds of times for one session is the signature of a cursor leak. Fix the application to close cursors in all code paths, including exception handlers.

Issue 3: Memory Pressure from High OPEN_CURSORS Value

Section titled “Issue 3: Memory Pressure from High OPEN_CURSORS Value”

Symptom: Setting OPEN_CURSORS to a very high value (e.g., 5000) causes shared pool or PGA memory pressure.

Cause: Oracle allocates memory structures proportional to OPEN_CURSORS per session. With many sessions and a very high limit, the cumulative overhead becomes significant.

Resolution: Rather than increasing OPEN_CURSORS indefinitely, investigate cursor caching and application behaviour. Tune SESSION_CACHED_CURSORS so that frequently reused cursors are cached rather than repeatedly opened. If using a connection pool, ensure connections are properly returned to the pool — stale connections holding open cursors inflate the count.

Section titled “Related Parameters”
ParameterRelationship
SESSION_CACHED_CURSORSCaches parsed cursors in the session to avoid repeated soft parses. Works alongside OPEN_CURSORS to manage cursor lifecycle efficiently.
CURSOR_SPACE_FOR_TIMEDeprecated in 12c. Previously kept cursors pinned in the shared pool. Leave at default (FALSE).
SHARED_POOL_SIZEThe shared pool holds parsed cursor representations. Insufficient shared pool space can cause cursor eviction, driving repeated hard parses.
Section titled “Related Errors”
ErrorDescription
ORA-01000Maximum Open Cursors Exceeded — the direct result of a session reaching the OPEN_CURSORS limit.
ORA-04031Unable to Allocate Shared Memory — indirectly related; shared pool exhaustion affects cursor caching.

Version Notes

Section titled “Version Notes”
Oracle VersionNotes
Oracle 7–10gDefault was 50. Typical production guidance was to set 300–500.
Oracle 11gDefault remained 50. V$OPEN_CURSOR view enhanced with SQL_ID column.
Oracle 12c+PDB-level modification supported. In a CDB, each PDB can set its own OPEN_CURSORS value, capped by the CDB root setting.
Oracle 19c–21cNo change to default or behaviour. Parameter remains relevant for all workload types.
Oracle 23aiNo change. Still a per-session limit; still defaults to 50.