ORA-01000: Maximum Open Cursors Exceeded in Oracle Database

Practical troubleshooting guide to identify open-cursor usage, diagnose cursor leaks, check session activity, and resolve ORA-01000 safely.

Last Updated: September 2026

ORA-01000: maximum open cursors exceeded occurs when an Oracle Database session attempts to have more open cursors than permitted by the OPEN_CURSORS initialization parameter.

The error is commonly associated with application code that does not close cursors properly, connection-pool behavior, excessive concurrent cursor usage, or an OPEN_CURSORS value that is too low for the workload.

Quick answer:

Do not automatically increase OPEN_CURSORS and consider the problem solved. First determine which session is approaching the limit and whether the application is leaking cursors. Increase the parameter only when the workload genuinely requires more simultaneously open cursors.

What Is ORA-01000?

Oracle raises ORA-01000 when a database session reaches the maximum number of cursors allowed by OPEN_CURSORS.

Oracle defines OPEN_CURSORS as the maximum number of open cursors, or handles to private SQL areas, that a session can have at one time. The value is session-oriented rather than a single database-wide cursor limit.

Typical ORA-01000 Error Message

ORA-01000: maximum open cursors exceeded

Depending on the application, the error may appear in an application log, Oracle alert/error log, middleware log, JDBC connection pool, Oracle E-Business Suite component, or another client application.

What Is an Oracle Cursor?

A cursor represents a session resource associated with processing SQL or PL/SQL statements. Oracle maintains session cursors for statements that are currently open or otherwise available through the cursor cache.

Explicit cursors are managed by application or PL/SQL code, while implicit cursors are managed by Oracle and PL/SQL. The dynamic performance view V$OPEN_CURSOR can be used to examine cursors associated with user sessions.

What Does OPEN_CURSORS Control?

The OPEN_CURSORS initialization parameter specifies the maximum number of open cursors a session can have simultaneously.

Check the current value with:

SHOW PARAMETER open_cursors;

Or:

SELECT name,
       value,
       isdefault,
       issys_modifiable
FROM v$parameter
WHERE name = 'open_cursors';

Common Causes of ORA-01000

1. Application Cursor Leak

A cursor leak occurs when application code repeatedly opens cursors but does not close them when they are no longer required.

This is one of the most important causes to investigate because simply increasing OPEN_CURSORS may only postpone the failure.

2. OPEN_CURSORS Is Too Low

An application may legitimately require a large number of simultaneously open cursors. In that situation, the configured value may be insufficient for the workload.

3. Connection Pool or Middleware Behavior

Application servers and connection pools can maintain database sessions for long periods. If cursors are not properly closed or reused, an individual session can accumulate a large number of open cursors.

4. Excessive Dynamic SQL

Applications that generate and execute large numbers of SQL statements dynamically should be reviewed for proper cursor management and statement reuse.

5. PL/SQL Cursor Management

Explicit cursors that remain open longer than necessary can contribute to excessive cursor usage. Application and PL/SQL code should close resources when they are no longer required.

Step 1 — Check the OPEN_CURSORS Setting

Start by determining the configured limit:

SELECT name,
       value
FROM v$parameter
WHERE name = 'open_cursors';

Record the value before making any configuration change.

Step 2 — Find Sessions at the OPEN_CURSORS Limit

The following query identifies sessions whose current open-cursor count has reached the configured limit:

SELECT s.username,
       s.sid,
       s.serial#,
       ss.value AS open_cursors
FROM v$sesstat ss
JOIN v$statname sn
  ON ss.statistic# = sn.statistic#
JOIN v$session s
  ON ss.sid = s.sid
WHERE sn.name = 'opened cursors current'
  AND ss.value = (
        SELECT TO_NUMBER(value)
        FROM v$parameter
        WHERE name = 'open_cursors'
      )
  AND s.username IS NOT NULL
ORDER BY ss.value DESC;

This is one of the first queries to run when investigating an active ORA-01000 incident.

Step 3 — Find Sessions With High Cursor Usage

Instead of looking only for sessions that have already reached the limit, identify sessions with high current cursor usage:

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

This helps identify sessions that are approaching the configured limit before the application actually fails.

Step 4 — Identify the SQL Behind Open Cursors

V$OPEN_CURSOR provides information about cursors that user sessions currently have open, parsed, or cached.

To summarize open cursors by session:

SELECT sid,
       user_name,
       COUNT(*) AS open_cursors
FROM v$open_cursor
WHERE user_name IS NOT NULL
GROUP BY sid, user_name
ORDER BY COUNT(*) DESC;

Step 5 — Examine Open Cursors for a Specific User

Replace APP_USER with the appropriate database username:

SELECT sid,
       user_name,
       sql_text,
       COUNT(*) AS open_cursors
FROM v$open_cursor
WHERE user_name = UPPER('APP_USER')
GROUP BY sid, user_name, sql_text
ORDER BY sid, COUNT(*) DESC;

Repeated SQL statements with unusually high cursor counts can provide useful clues about the application behavior.

Step 6 — Examine Cursors for a Specific SID

If you have identified a problematic session, inspect its cursors directly:

SELECT sid,
       user_name,
       sql_text,
       COUNT(*) AS open_cursors
FROM v$open_cursor
WHERE sid = 123
GROUP BY sid, user_name, sql_text
ORDER BY COUNT(*) DESC;

Replace 123 with the actual SID.

Step 7 — Determine the Highest Cursor Usage

The following query compares session cursor usage with the configured OPEN_CURSORS value:

SELECT s.sid,
       s.username,
       MAX(ss.value) AS highest_open_cursors,
       p.value AS max_open_cursors
FROM v$session s
JOIN v$sesstat ss
  ON ss.sid = s.sid
JOIN v$statname sn
  ON sn.statistic# = ss.statistic#
CROSS JOIN v$parameter p
WHERE sn.name = 'opened cursors current'
  AND p.name = 'open_cursors'
  AND s.username IS NOT NULL
GROUP BY s.sid, s.username, p.value
ORDER BY MAX(ss.value) DESC;

This is useful when determining whether the configured limit is appropriate for normal workload requirements.

How to Determine Whether You Have a Cursor Leak

A high cursor count alone does not prove that an application has a cursor leak. The DBA should compare current and historical behavior and identify the sessions and SQL statements responsible.

Look for patterns such as:

The same application session continuously increasing its cursor count.
Sessions repeatedly opening cursors without releasing them.
Cursor counts growing after repeated application transactions.
Connection-pool sessions retaining resources longer than expected.
A small number of sessions consuming unusually large numbers of cursors.
Application errors appearing after long-running sessions accumulate cursor usage.

Solution 1 — Correct the Application Cursor Management

If the investigation identifies a cursor leak, the preferred solution is to correct the application or middleware code.

The application should ensure that cursors, statements, result sets, and related database resources are properly released when they are no longer required.

Best practice:

Fixing a cursor leak is preferable to repeatedly increasing OPEN_CURSORS. Otherwise, the same problem can eventually return at a higher threshold.

Solution 2 — Increase OPEN_CURSORS When Justified

If analysis confirms that the application legitimately requires more simultaneously open cursors, the DBA can increase OPEN_CURSORS to an appropriate value.

First review the current setting:

SHOW PARAMETER open_cursors;

Then, for a justified change, use an appropriate value for your Oracle environment:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

The example value 1000 is illustrative only. Do not copy it blindly into production. Determine the required value from actual workload measurements and application behavior.

Should You Always Increase OPEN_CURSORS?

No.

Increasing OPEN_CURSORS is appropriate when the workload genuinely requires a higher limit. It is not a substitute for fixing application code that continuously opens and retains cursors.

Oracle documentation notes that the value should be high enough to prevent applications from running out of open cursors, and that the appropriate value varies between applications.

Example Troubleshooting Scenario

Assume an application reports:

ORA-01000: maximum open cursors exceeded

The DBA checks:

SELECT name, value
FROM v$parameter
WHERE name = 'open_cursors';

Suppose the configured limit is 300.

The DBA then checks session usage:

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

If one application session is repeatedly approaching 300 while other sessions remain well below the limit, investigate that application's cursor management and the SQL associated with the session.

If analysis confirms that the application legitimately needs more than 300 cursors, increasing OPEN_CURSORS may be appropriate. If the count continues to grow without a legitimate workload reason, investigate a cursor leak instead.

Oracle E-Business Suite and Application Environments

In Oracle E-Business Suite and other enterprise application environments, ORA-01000 should be investigated at both the database and application tiers.

Check:

The database session associated with the error.
The application username.
Concurrent or background processing activity.
Connection-pool configuration.
Application logs around the time of the failure.
SQL statements associated with the affected session.
Whether cursor usage increases progressively during repeated transactions.

Avoid changing database parameters without first correlating the database session with the application workload.

RAC Considerations

In an Oracle RAC environment, investigate the affected instance and session rather than treating cursor usage as a single undifferentiated database-wide number.

Identify the session and instance involved, then investigate the application workload associated with that session.

Common Mistakes When Fixing ORA-01000

Blindly increasing OPEN_CURSORS: This can hide a cursor leak instead of fixing it.
Restarting the database as the only solution: A restart may temporarily clear session state but does not correct defective application behavior.
Looking only at the parameter: The configured limit does not explain which application session is consuming the cursors.
Ignoring connection pools: Long-lived pooled sessions can expose resource-management problems that are not obvious during short tests.
Ignoring application logs: The database session and application log should be correlated when possible.
Changing production settings without measurement: Determine actual cursor usage before selecting a new limit.

Recommended DBA Troubleshooting Sequence

Capture the ORA-01000 error and timestamp.
Identify the affected database user and session.
Check the current OPEN_CURSORS value.
Measure current cursor usage across sessions.
Identify sessions approaching or reaching the limit.
Inspect V$OPEN_CURSOR for the affected session.
Correlate the session with application activity and logs.
Determine whether the workload legitimately needs more cursors.
Fix application cursor management if a leak is identified.
Increase OPEN_CURSORS only when justified by workload requirements.
Monitor the environment after the change.

Prevention and Monitoring

Preventing ORA-01000 is better than repeatedly responding to the error after production failures occur.

Monitor sessions with unusually high cursor usage.
Establish a reasonable OPEN_CURSORS value based on workload.
Review application connection-pool behavior.
Ensure application developers properly close database resources.
Investigate increasing cursor counts before the configured limit is reached.
Review application changes that introduce new SQL or database-access logic.

Quick Diagnostic SQL Reference

Check OPEN_CURSORS

SHOW PARAMETER open_cursors;

Find Current Cursor Usage

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

Find Open Cursors by Session

SELECT sid,
       user_name,
       COUNT(*) AS open_cursors
FROM v$open_cursor
WHERE user_name IS NOT NULL
GROUP BY sid, user_name
ORDER BY COUNT(*) DESC;

Inspect a Specific Session

SELECT sid,
       user_name,
       sql_text,
       COUNT(*) AS open_cursors
FROM v$open_cursor
WHERE sid = 123
GROUP BY sid, user_name, sql_text
ORDER BY COUNT(*) DESC;

Frequently Asked Questions

What causes ORA-01000?

The error occurs when a session attempts to open more cursors than permitted by OPEN_CURSORS. Common causes include cursor leaks, excessive concurrent cursor requirements, and an insufficient parameter value.

Is ORA-01000 a database problem or an application problem?

It can involve either. The database enforces the OPEN_CURSORS limit, but the underlying cause may be application resource management or a workload that legitimately requires a higher limit.

Does increasing OPEN_CURSORS fix ORA-01000?

It can resolve the error when the configured limit is genuinely too low. However, if the application is leaking cursors, increasing the limit may only delay the next failure.

How do I check OPEN_CURSORS?

SHOW PARAMETER open_cursors;

How do I find the session consuming many cursors?

Query V$SESSTAT, V$STATNAME, and V$SESSION for the opened cursors current statistic, then inspect the affected session in V$OPEN_CURSOR.

Should I restart Oracle when ORA-01000 occurs?

A restart can clear session state, but it should not be considered the permanent solution. Determine why the session reached the cursor limit and correct the underlying problem.

Can connection pools cause ORA-01000?

Yes. Long-lived pooled sessions can expose cursor-management problems when statements or other resources are not properly released.

Final DBA Checklist

☐ Capture the ORA-01000 error and timestamp.
☐ Check the current OPEN_CURSORS value.
☐ Identify sessions with high cursor usage.
☐ Identify the affected application user and SID.
☐ Review V$OPEN_CURSOR.
☐ Correlate database activity with application logs.
☐ Determine whether a cursor leak exists.
☐ Determine whether the workload legitimately requires more cursors.
☐ Correct application resource management where required.
☐ Increase OPEN_CURSORS only when justified.
☐ Monitor cursor usage after remediation.

Conclusion

ORA-01000 is best treated as a diagnostic problem rather than simply a parameter-setting problem. The correct approach is to identify the affected session, measure cursor usage, inspect the open cursors, determine whether the workload or application is responsible, and then apply the appropriate fix.

If the application genuinely requires more simultaneously open cursors, increase OPEN_CURSORS based on measured requirements. If cursor usage is caused by a leak, fix the application or middleware resource-management problem instead.

Following this approach helps resolve the immediate ORA-01000 error while reducing the risk of repeated failures in production.

Related Oracle DBA Topics

Disclaimer: The commands and examples in this article are provided for educational and troubleshooting purposes. Test changes in a non-production environment before applying them to production systems. Always validate configuration changes against your Oracle Database version, workload, application requirements, and change-management procedures.

Oracle Database Error Solutions & DBA Knowledge Base

ORA-01000: Maximum Open Cursors Exceeded – Causes, Diagnosis and Solutions