📅 Last Updated: August 2026

This guide explains ORA-01000: maximum open cursors exceeded, why Oracle raises it, how the OPEN_CURSORS parameter works, and what DBAs should investigate before increasing the parameter.

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

ORA-01000: maximum open cursors exceeded is an Oracle Database error that occurs when a database session attempts to open more cursors than permitted by the session's configured OPEN_CURSORS limit.

The error is commonly encountered in applications that repeatedly execute SQL or PL/SQL without correctly closing cursors. It can also occur when a legitimate workload requires more simultaneously open cursors than the current configuration allows.

Although increasing OPEN_CURSORS can resolve the immediate error in some environments, it should not automatically be considered the root-cause fix. A persistent ORA-01000 can indicate an application-side cursor leak, poorly managed database connections, excessive SQL activity, or an incorrectly sized database parameter.

Important:

Do not immediately increase OPEN_CURSORS simply to make the error disappear. First determine whether the session is legitimately opening many cursors or whether cursors are accumulating because the application or PL/SQL code is not closing them correctly.

What Is ORA-01000?

The Oracle error message is:

ORA-01000: maximum open cursors exceeded

Oracle raises this error when a session exceeds the maximum number of simultaneously open cursors allowed by the OPEN_CURSORS initialization parameter.

A simplified example is:

OPEN_CURSORS = 300

If a session attempts to exceed its permitted number of open cursors, Oracle can return:

ORA-01000: maximum open cursors exceeded

The important point is that the limit applies to an individual database session, not to the entire database as one shared cursor counter.

What Is a Cursor?

A cursor is a mechanism Oracle uses to process SQL statements and retrieve or manipulate data.

When an application submits SQL to Oracle, Oracle parses and executes the statement. Depending on the type of operation and execution path, cursor structures are associated with that SQL execution.

Cursors are particularly important for:

SQL statements.
PL/SQL statements.
Queries returning rows.
Explicit PL/SQL cursors.
Dynamic SQL.
Application-generated SQL.

Applications normally open, use, and release cursors as part of their database interaction.

When cursor resources remain open unnecessarily, the number of open cursors in a session can continue to increase.

What Is OPEN_CURSORS?

OPEN_CURSORS is an Oracle initialization parameter that specifies the maximum number of cursors that a session can have open simultaneously.

You can check its current value using:

SHOW PARAMETER open_cursors;

Example:

NAME          TYPE        VALUE
------------- ----------- -----
open_cursors  integer     300

The actual value in your database may be different.

Check OPEN_CURSORS Using SQL

You can also query the initialization parameter directly:

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

This is useful when you are writing diagnostic scripts or need to collect the parameter value programmatically.

Is OPEN_CURSORS a Database-Wide Cursor Limit?

No.

This distinction is extremely important.

OPEN_CURSORS defines a per-session limit.

For example, if:

OPEN_CURSORS = 300

then one session may have up to its configured limit of open cursors, while another session has its own independent cursor usage.

Therefore, having thousands of sessions does not mean that every session is collectively restricted to only 300 cursors.

Open Cursors vs Shared SQL

Another common source of confusion is the difference between an open cursor in a session and a SQL statement stored in Oracle's shared SQL area.

Oracle can share SQL between sessions when statements are identical or otherwise shareable.

However, a session can still have cursors open for SQL statements even when the underlying SQL has shared execution structures.

Therefore:

OPEN_CURSORS is a session-level limit.
The shared pool contains shared SQL and related structures.
Cursor usage should not be confused with total SQL statements in the database.

Common Symptoms of ORA-01000

The exact symptom depends on the application and workload.

Common symptoms include:

Application transactions suddenly fail.
SQL statements return ORA-01000.
PL/SQL procedures fail intermittently.
Reports fail while other application functions continue working.
Only specific application sessions encounter the error.
The error becomes more frequent as a long-running session remains active.
Restarting the application temporarily clears the problem.

The last symptom is particularly useful diagnostically.

If restarting an application or disconnecting a session temporarily resolves the problem and the error returns after the session has been running for some time, investigate cursor accumulation.

Why Does ORA-01000 Happen?

There are several possible causes.

1. Application Cursor Leak

The application opens cursors but does not close them properly.

This is one of the most important causes to investigate.

2. PL/SQL Code Does Not Close Explicit Cursors

A PL/SQL program may repeatedly open explicit cursors without correctly closing them.

3. Excessive Simultaneously Open SQL Statements

Some applications legitimately maintain many cursors at the same time.

4. Poor Connection Management

Long-lived database sessions can accumulate cursor usage if application resources are not properly released.

5. Incorrectly Sized OPEN_CURSORS

The application may simply require more open cursors than the configured limit.

6. Dynamic SQL

Applications that generate large numbers of distinct SQL statements can increase cursor usage.

A Cursor Leak vs High Cursor Requirement

These two scenarios should not be treated as the same problem.

Scenario	Typical Behavior	Likely Action
Application legitimately needs many cursors	Cursor usage is consistently high but stable	Evaluate and potentially increase `OPEN_CURSORS`
Cursor leak	Cursor usage continually increases during session lifetime	Fix application or PL/SQL resource management
Long-running session	Usage accumulates over time	Investigate session/application lifecycle
One specific module	Only certain application functions cause the increase	Trace and fix the affected module

This distinction is critical.

Increasing OPEN_CURSORS can provide additional capacity, but it does not necessarily fix a cursor leak.

Why Restarting the Application May Temporarily Fix the Error

Suppose an application session gradually accumulates open cursors.

Eventually it reaches the configured limit:

OPEN_CURSORS = 300

The session then encounters:

ORA-01000: maximum open cursors exceeded

If the application is restarted, its database sessions are disconnected.

The associated session resources are released, and newly created sessions start with fresh cursor usage.

The error may therefore disappear temporarily.

However, if the underlying application code continues leaking cursors, the problem will eventually return.

Diagnostic clue:

If restarting an application repeatedly fixes ORA-01000 only for a limited period, investigate cursor accumulation before treating the problem as a simple parameter-sizing issue.

Explicit Cursors in PL/SQL

PL/SQL supports explicit cursors that developers can open and close.

For example:

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, employee_name
        FROM employees;

    v_employee_id   employees.employee_id%TYPE;
    v_employee_name employees.employee_name%TYPE;

BEGIN

    OPEN c_emp;

    LOOP

        FETCH c_emp
        INTO v_employee_id, v_employee_name;

        EXIT WHEN c_emp%NOTFOUND;

        -- Process employee

    END LOOP;

    CLOSE c_emp;

END;
/

The important point is that an explicitly opened cursor should be properly closed when it is no longer required.

What Happens If a Cursor Is Not Closed?

Consider poorly managed code that repeatedly opens cursors without closing them.

Conceptually:

OPEN cursor_1
OPEN cursor_2
OPEN cursor_3
...
OPEN cursor_N

If those cursors remain open unnecessarily, session cursor usage can increase.

Repeated execution of such code can eventually push the session toward the OPEN_CURSORS limit.

Implicit vs Explicit Cursors

Oracle also manages many cursors implicitly.

Therefore, seeing cursor activity does not automatically mean that application code contains an explicit OPEN statement.

For example, SQL executed by an application can involve cursors even when the developer never manually declares an explicit PL/SQL cursor.

This is why database-level investigation is important when diagnosing ORA-01000.

Application Connection Pools

Connection pooling can complicate ORA-01000 troubleshooting.

A connection pool may maintain a number of persistent Oracle sessions.

If application code obtains a connection and does not properly close its statement/result-set resources before returning the connection to the pool, cursor resources can remain associated with the session.

The database session itself may remain alive for a long time.

This can cause cursor usage to accumulate over a much longer period than expected.

Why Long-Lived Sessions Matter

A short-lived session may terminate before a cursor leak becomes visible.

A long-lived application session can expose the problem much more clearly.

For example:

Application starts
       ↓
Database session created
       ↓
SQL executed
       ↓
Cursor opened
       ↓
Cursor not released correctly
       ↓
More requests
       ↓
More cursors
       ↓
Cursor count increases
       ↓
OPEN_CURSORS limit reached
       ↓
ORA-01000

This pattern is a strong indication that the application should be investigated for resource-management problems.

Does Increasing OPEN_CURSORS Consume Memory?

Increasing OPEN_CURSORS allows a session to have more cursors open simultaneously.

However, increasing the parameter should still be done thoughtfully because cursor-related session resources consume memory.

A very large value should not be selected simply because it is technically possible.

The appropriate value depends on the application's requirements and the database environment.

What Is a Reasonable OPEN_CURSORS Value?

There is no single universal value that is correct for every Oracle database.

The appropriate setting depends on:

Application architecture.
Number of simultaneously open cursors.
Connection-pool behavior.
PL/SQL workload.
Number of SQL statements used by the application.
Oracle Database version.
Available memory.

Values such as 300, 500, 1000, or higher may be appropriate in different environments, but the value should be based on observed workload rather than copied from another database.

Initial Diagnostic Query

Before changing the parameter, determine how much cursor capacity is actually being used.

The following query can be used as an initial diagnostic:

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       ss.value AS opened_cursors_current
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'
ORDER BY ss.value DESC;

This helps identify sessions with high current open-cursor usage.

A session approaching the configured OPEN_CURSORS value deserves further investigation.

Check the Maximum Open Cursors Used

Oracle also exposes a session statistic for the maximum number of cursors that have been open simultaneously.

For example:

SELECT s.sid,
       s.serial#,
       s.username,
       ss.value AS opened_cursors_cumulative
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'
ORDER BY ss.value DESC;

For detailed analysis, Part 2 will expand these queries and show how to identify the SQL and sessions responsible for high cursor usage.

Important Difference: Current vs Cumulative Cursor Statistics

Oracle exposes several cursor-related statistics, and their meanings should not be confused.

For example:

opened cursors current indicates the number of currently open cursors associated with a session.
opened cursors cumulative represents the cumulative number of cursors opened by a session over its lifetime.

A high cumulative number does not necessarily mean that the session currently has a cursor leak.

A session can legitimately open and close a large number of cursors during normal operation.

For ORA-01000, current open-cursor usage is especially important.

Detailed Diagnosis of ORA-01000

Once ORA-01000: maximum open cursors exceeded occurs, the next step is to determine which session is consuming the cursors and why.

Simply increasing OPEN_CURSORS without investigating the affected session can hide an application problem and allow cursor usage to continue growing.

The following diagnostic steps can be performed by an Oracle DBA to identify the source of excessive cursor usage.

Step 1 – Check the Current OPEN_CURSORS Setting

Start by checking the configured value:

SHOW PARAMETER open_cursors;

Alternatively:

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

Record the current value before making any changes.

Step 2 – Find Sessions with High Current Cursor Usage

The following query is useful for identifying sessions that currently have a large number of open cursors:

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.program,
       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 query provides a useful overview of cursor usage across active database sessions.

Pay particular attention to sessions whose cursor count is close to the configured OPEN_CURSORS value.

Step 3 – Identify the Application and Machine

Once a session with high cursor usage has been identified, determine where the session originated.

SELECT sid,
       serial#,
       username,
       status,
       machine,
       program,
       module,
       action,
       logon_time
FROM v$session
WHERE sid = :sid;

Replace :sid with the SID identified during the previous step.

The following columns are particularly useful:

MACHINE – identifies the client machine.
PROGRAM – identifies the client program.
MODULE – can identify the application module.
ACTION – can provide additional application context.
LOGON_TIME – shows how long the session has existed.

Step 4 – Check the Session's Current SQL

After identifying the session, inspect its current SQL:

SELECT sid,
       serial#,
       sql_id,
       sql_child_number,
       event,
       status
FROM v$session
WHERE sid = :sid;

If a SQL_ID is available, retrieve the SQL text:

SELECT sql_id,
       child_number,
       parsing_schema_name,
       executions,
       sql_text
FROM v$sql
WHERE sql_id = :sql_id;

The current SQL is useful, but it is not necessarily the statement responsible for all open cursors.

Cursor accumulation may be caused by statements executed earlier during the lifetime of the session.

Step 5 – Investigate V$OPEN_CURSOR

The V$OPEN_CURSOR view is one of the most useful views when diagnosing ORA-01000.

It provides information about cursors currently open and cached by sessions.

A basic query is:

SELECT sid,
       user_name,
       sql_id,
       cursor_type,
       sql_text
FROM v$open_cursor
WHERE sid = :sid
ORDER BY sql_id;

This allows the DBA to examine the cursors associated with a particular session.

Step 6 – Count Cursors for a Specific Session

Instead of displaying every cursor, first obtain a count:

SELECT sid,
       COUNT(*) AS cursor_count
FROM v$open_cursor
WHERE sid = :sid
GROUP BY sid;

Compare this value with the session statistic obtained from V$SESSTAT.

The values should not automatically be expected to match exactly because the underlying views and statistics have different purposes and semantics.

Use them together as diagnostic evidence rather than treating one query as the definitive cursor count for every purpose.

Step 7 – Group Open Cursors by Cursor Type

A useful investigation technique is to group the session's cursors by CURSOR_TYPE.

SELECT cursor_type,
       COUNT(*) AS cursor_count
FROM v$open_cursor
WHERE sid = :sid
GROUP BY cursor_type
ORDER BY cursor_count DESC;

This can help identify whether a large portion of the session's cursor entries belongs to a particular cursor category.

Step 8 – Find SQL Statements Repeatedly Appearing in V$OPEN_CURSOR

Another useful diagnostic is to group cursor entries by SQL text.

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

This can reveal statements that appear repeatedly for the affected session.

Repeated entries do not automatically prove a cursor leak, but they provide an important clue for further investigation.

Step 9 – Group by SQL_ID

Grouping by SQL_ID can be more useful than grouping by the full SQL text.

SELECT sql_id,
       COUNT(*) AS cursor_count
FROM v$open_cursor
WHERE sid = :sid
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY cursor_count DESC;

After identifying a suspicious SQL_ID, investigate it through V$SQL.

Step 10 – Examine SQL Execution Information

For a suspicious SQL statement:

SELECT sql_id,
       child_number,
       executions,
       parse_calls,
       loads,
       invalidations,
       users_opening,
       users_executing,
       parsing_schema_name,
       module,
       action
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

Several columns can provide valuable clues.

EXECUTIONS – number of executions.
PARSE_CALLS – number of parse calls.
INVALIDATIONS – number of invalidations.
USERS_OPENING – number of users currently opening the cursor.
USERS_EXECUTING – number of users currently executing the cursor.
MODULE – application module when provided.
ACTION – application action when provided.

Step 11 – Look for Excessive Child Cursors

A single SQL statement can have multiple child cursors.

You can investigate the number of child cursors using:

SELECT sql_id,
       COUNT(*) AS child_cursor_count
FROM v$sql
WHERE sql_id = :sql_id
GROUP BY sql_id;

If a SQL statement has a large number of child cursors, investigate why Oracle is creating multiple child versions.

Potential reasons can include differences in optimizer environment, bind metadata, object definitions, or other conditions affecting cursor sharing.

However, excessive child cursors are a separate issue from a simple application cursor leak and should be diagnosed using the appropriate Oracle views and version-specific information.

Step 12 – Check Session Statistics

The following query provides both current and cumulative cursor-related statistics:

SELECT s.sid,
       s.serial#,
       s.username,
       sn.name,
       ss.value
FROM v$session s
JOIN v$sesstat ss
  ON ss.sid = s.sid
JOIN v$statname sn
  ON sn.statistic# = ss.statistic#
WHERE s.sid = :sid
  AND sn.name IN (
      'opened cursors current',
      'opened cursors cumulative'
  )
ORDER BY sn.name;

This is useful because it separates two very different situations.

Current Open Cursors vs Cumulative Open Cursors

Statistic	Meaning	Diagnostic Importance
`opened cursors current`	Current open-cursor usage for the session.	Very important when investigating ORA-01000.
`opened cursors cumulative`	Total cursors opened by the session over its lifetime.	Useful for understanding workload, but not equivalent to current open cursors.

A session can have a high cumulative count while maintaining a relatively low current count because it may be opening and closing cursors normally.

Therefore, a high cumulative value alone does not prove a cursor leak.

Step 13 – Determine Whether Cursor Usage Is Increasing

A single snapshot is useful, but repeated measurements are much more valuable.

For example, record the current open-cursor count:

SELECT ss.value AS opened_cursors_current
FROM v$sesstat ss
JOIN v$statname sn
  ON sn.statistic# = ss.statistic#
WHERE ss.sid = :sid
  AND sn.name = 'opened cursors current';

Run the query again after the affected application performs several transactions.

If the value repeatedly increases and does not return to a lower level after work completes, this is an important indication that resources may not be getting released as expected.

Step 14 – Monitor the Same Session Over Time

For example, you might observe:

Time	Current Open Cursors
09:00	75
09:30	120
10:00	175
10:30	240
11:00	295

If OPEN_CURSORS is 300, this pattern strongly suggests that the session is approaching the configured limit.

The important observation is not simply the value of 295.

The important observation is the continuous increase.

Step 15 – Identify the Application Module

If the application sets module and action information using DBMS_APPLICATION_INFO, those values can be extremely useful.

SELECT sid,
       serial#,
       username,
       machine,
       program,
       module,
       action,
       client_identifier
FROM v$session
WHERE sid = :sid;

If the application consistently identifies modules, the DBA can determine which application component is associated with excessive cursor usage.

Step 16 – Identify the Client Program

The PROGRAM column can help determine whether the affected session originates from:

Oracle Forms.
JDBC applications.
OCI applications.
SQL*Plus.
Oracle E-Business Suite components.
Web application servers.
Background jobs.
Other database clients.

For example:

SELECT sid,
       serial#,
       username,
       machine,
       program,
       module,
       action
FROM v$session
WHERE username IS NOT NULL
ORDER BY username, machine;

Step 17 – Check for Long-Lived Sessions

A cursor leak is more likely to become visible in a long-lived session.

Check session logon times:

SELECT sid,
       serial#,
       username,
       status,
       machine,
       program,
       logon_time
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time;

If the affected session has been running for days or weeks and its current cursor usage has gradually increased, investigate the application lifecycle.

Step 18 – Investigate Connection Pooling

If the application uses a connection pool, determine:

Minimum pool size.
Maximum pool size.
Connection lifetime.
Idle connection timeout.
Statement caching configuration.
How statements and result sets are closed.
Whether connections are reset before being returned to the pool.

A database session that remains permanently attached to an application connection can retain session-level resources for a long time.

Step 19 – Investigate PL/SQL Cursor Usage

If the problem occurs inside a PL/SQL procedure or package, review explicit cursor handling.

A typical cursor lifecycle is:

OPEN
  ↓
FETCH
  ↓
PROCESS
  ↓
CLOSE

The application should ensure that cursors opened explicitly are closed appropriately.

For exception-prone code, review all execution paths, including exception handlers, to ensure resources are not left open unnecessarily.

Step 20 – Use FOR Loops Where Appropriate

PL/SQL cursor FOR loops can simplify cursor lifecycle management.

For example:

BEGIN

    FOR r IN (
        SELECT employee_id,
               employee_name
        FROM employees
    )
    LOOP

        DBMS_OUTPUT.PUT_LINE(
            r.employee_id || ' ' || r.employee_name
        );

    END LOOP;

END;
/

This approach allows PL/SQL to manage the cursor lifecycle associated with the loop automatically.

It can reduce the risk of forgetting to explicitly close a cursor.

Step 21 – Investigate Dynamic SQL

Applications that generate SQL dynamically should be reviewed carefully.

For example, repeatedly generating different SQL text such as:

SELECT * FROM employees WHERE employee_id = 101;

SELECT * FROM employees WHERE employee_id = 102;

SELECT * FROM employees WHERE employee_id = 103;

can create many distinct SQL statements.

Using bind variables where appropriate can improve cursor sharing and reduce unnecessary hard parsing.

For example:

SELECT *
FROM employees
WHERE employee_id = :employee_id;

However, bind variables alone do not fix a cursor leak if the application is failing to close statement resources.

Step 22 – Check for Repeated SQL Text

A useful investigation is to identify SQL statements that differ only because literal values are embedded directly into the SQL.

For example:

SELECT sql_id,
       executions,
       parse_calls,
       sql_text
FROM v$sql
WHERE parsing_schema_name = 'SCOTT'
ORDER BY parse_calls DESC;

Look for large numbers of statements with similar SQL structures but different literal values.

This can indicate inefficient SQL generation and may contribute to parsing and cursor-management problems.

Step 23 – Determine Whether the Problem Is Application-Specific

Ask the following questions:

Does ORA-01000 occur for all users?
Does it occur only for one application?
Does it occur only for one application module?
Does it occur only on one application server?
Does it occur only after the application has been running for a long time?
Does restarting the application temporarily resolve the problem?
Does the problem follow a particular database session?

The answers can significantly narrow the investigation.

Step 24 – Check Whether Only One Session Is Affected

If most sessions have low cursor usage but one or a few sessions are close to the limit, the problem is more likely to be associated with a particular application process or workload.

For example:

SID   USERNAME   PROGRAM              OPEN_CURSORS
----  ---------  -------------------  ------------
101   APPUSER    JDBC Thin Client     287
102   APPUSER    JDBC Thin Client      64
103   APPUSER    JDBC Thin Client      51
104   APPUSER    JDBC Thin Client      72

In this example, session 101 deserves immediate investigation.

Step 25 – Check Whether the Application Is Opening Too Many Statements

Application developers should verify that database resources are released correctly.

For JDBC-based applications, for example, the application should appropriately manage:

Connection objects.
Prepared statements.
Callable statements.
Result sets.

The exact resource-management pattern depends on the framework and application architecture.

The DBA should work with the development team rather than assuming that the database parameter alone is responsible.

Step 26 – Check Oracle E-Business Suite Environments

In Oracle E-Business Suite environments, ORA-01000 should be investigated in the context of the specific application tier component generating the database sessions.

Determine whether the problem originates from:

Forms sessions.
Concurrent processing.
Application server components.
Custom PL/SQL.
Custom reports.
Custom interfaces.
Database packages.

Customizations should receive particular attention because custom code may not follow the same resource-management patterns as standard Oracle E-Business Suite components.

Step 27 – Do Not Kill Sessions as the First Solution

A DBA can terminate an affected session to release its session resources, but this should generally be considered an operational workaround rather than a permanent solution.

For example:

ALTER SYSTEM KILL SESSION 'sid,serial#';

The exact command and behavior depend on the Oracle version and session state.

Production Warning:

Do not terminate production sessions without understanding the business impact. Killing an application session may roll back work, interrupt a transaction, or affect users.

Step 28 – Determine Whether Increasing OPEN_CURSORS Is Justified

After collecting diagnostic information, determine whether the application legitimately requires a larger cursor limit.

Increasing the parameter is more reasonable when:

Cursor usage is high but stable.
The application is known to legitimately keep many cursors open.
No evidence of a cursor leak exists.
The application vendor recommends a higher value.
The database has sufficient resources.
Testing confirms that the higher value resolves the problem without introducing other issues.

If cursor usage continuously increases over time, fix the underlying resource-management problem instead.

How to Distinguish a Leak from Normal Usage

Observation	Likely Interpretation
Cursor count rises and falls with workload	May be normal application behavior.
Cursor count remains consistently high	Application may legitimately require a higher limit.
Cursor count continually rises without falling	Possible cursor/resource leak.
Only one module causes the increase	Investigate that module.
Restarting application resets the problem	Strong reason to investigate session/resource accumulation.
All application sessions reach the limit	Could indicate configuration or common application behavior.

A Practical Investigation Workflow

For production troubleshooting, the following sequence is recommended:

ORA-01000 occurs
      ↓
Check OPEN_CURSORS
      ↓
Find sessions with high current cursor usage
      ↓
Identify SID / SERIAL#
      ↓
Identify machine / program / module
      ↓
Inspect V$OPEN_CURSOR
      ↓
Identify repeated SQL / SQL_ID
      ↓
Review application behavior
      ↓
Determine whether usage is stable or increasing
      ↓
Is there a cursor leak?
      ├── YES → Fix application / PL/SQL
      │
      └── NO
           ↓
     Is higher capacity justified?
           ↓
     Evaluate OPEN_CURSORS increase

Useful Diagnostic Query – Complete Session Overview

The following query combines several useful session attributes with current cursor usage:

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.program,
       s.module,
       s.action,
       s.logon_time,
       ss.value AS opened_cursors_current
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 is a good starting query for an Oracle DBA who needs to identify the sessions closest to the configured cursor limit.

Useful Diagnostic Query – Top Sessions by Cursor Usage

For a quick investigation:

SELECT *
FROM (
    SELECT s.sid,
           s.serial#,
           s.username,
           s.machine,
           s.program,
           s.module,
           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
)
WHERE ROWNUM <= 20;

This returns the top sessions by current open-cursor usage.

Solutions for ORA-01000: Maximum Open Cursors Exceeded

After identifying the affected session and determining the reason for excessive cursor usage, the next step is to apply the appropriate solution.

There is no single solution for every ORA-01000 incident. The correct fix depends on whether the problem is caused by an application cursor leak, PL/SQL code, connection-pool behavior, excessive SQL activity, or an insufficient OPEN_CURSORS setting.

Solution 1 – Increase OPEN_CURSORS

If investigation confirms that the application legitimately requires more simultaneously open cursors, the OPEN_CURSORS parameter can be increased.

First check the current value:

SHOW PARAMETER open_cursors;

For example, if the current value is:

open_cursors    integer    300

the DBA may evaluate a higher value such as 500 or 1000, depending on the workload and Oracle environment.

Do not select a new value simply because another database uses it. The value should be based on actual workload requirements.

Changing OPEN_CURSORS

For an Oracle database where the parameter is dynamically modifiable, a DBA can change it using:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

Verify the new value:

SHOW PARAMETER open_cursors;

You can also verify it through the data dictionary:

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

Important:

Always verify the Oracle Database version, parameter behavior, application requirements, and change-management procedures before modifying a production database parameter.

SCOPE=BOTH, MEMORY and SPFILE

Oracle provides different parameter scopes.

SCOPE=MEMORY

ALTER SYSTEM SET open_cursors = 1000 SCOPE=MEMORY;

This changes the running instance but does not persist the change in the server parameter file.

SCOPE=SPFILE

ALTER SYSTEM SET open_cursors = 1000 SCOPE=SPFILE;

This stores the change in the server parameter file for subsequent instance startup, subject to the parameter's modifiability and database configuration.

SCOPE=BOTH

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

This attempts to apply the change to the running instance and persist it.

For production environments, use the scope appropriate for your Oracle architecture and change-management policy.

Solution 2 – Fix the Application Cursor Leak

If investigation shows that cursor usage continuously increases because application resources are not released correctly, increasing OPEN_CURSORS is only a temporary workaround.

The application should be reviewed to ensure that database resources are correctly released.

Typical resources that require proper lifecycle management include:

Connections.
Statements.
Prepared statements.
Callable statements.
Result sets.
Explicit PL/SQL cursors.

The exact implementation depends on the programming language and database framework.

Solution 3 – Correct Explicit PL/SQL Cursor Handling

When explicit cursors are used in PL/SQL, make sure that they are closed appropriately.

A typical pattern is:

DECLARE

    CURSOR c_emp IS
        SELECT employee_id,
               employee_name
        FROM employees;

BEGIN

    OPEN c_emp;

    LOOP

        FETCH c_emp
        INTO v_employee_id,
             v_employee_name;

        EXIT WHEN c_emp%NOTFOUND;

        -- Processing

    END LOOP;

    CLOSE c_emp;

END;
/

The actual code should also account for exception paths and ensure that explicitly opened resources are not unnecessarily left open.

Solution 4 – Prefer Cursor FOR Loops When Appropriate

For straightforward cursor processing, a PL/SQL cursor FOR loop can simplify cursor management.

BEGIN

    FOR r IN (
        SELECT employee_id,
               employee_name
        FROM employees
    )
    LOOP

        DBMS_OUTPUT.PUT_LINE(
            r.employee_id || ' - ' || r.employee_name
        );

    END LOOP;

END;
/

This avoids manually managing the normal open, fetch, and close lifecycle for the cursor associated with the loop.

Solution 5 – Review Dynamic SQL

Dynamic SQL should be reviewed carefully when investigating cursor-related problems.

For example, an application that repeatedly generates different SQL text:

SELECT * FROM employees WHERE employee_id = 101;

SELECT * FROM employees WHERE employee_id = 102;

SELECT * FROM employees WHERE employee_id = 103;

may create many distinct SQL statements.

Where appropriate, use bind variables:

SELECT *
FROM employees
WHERE employee_id = :employee_id;

Bind variables can improve cursor sharing and reduce unnecessary parsing.

However, remember that bind variables do not replace proper cursor and statement resource management.

Solution 6 – Review JDBC Statement Handling

For Java applications using JDBC, developers should ensure that database resources are properly closed.

The resources commonly involved include:

Connection
Statement
PreparedStatement
CallableStatement
ResultSet

Modern Java applications commonly use structured resource-management techniques such as try-with-resources.

The exact implementation should follow the application's JDBC framework and coding standards.

Solution 7 – Review Connection Pool Configuration

If the application uses a connection pool, review its configuration with the application team.

Important settings can include:

Maximum pool size.
Minimum pool size.
Connection timeout.
Idle timeout.
Connection lifetime.
Statement cache configuration.
Validation settings.

The objective is not simply to reduce the number of connections.

The objective is to ensure that database sessions are reused safely and that application resources associated with each session are correctly released.

Solution 8 – Review Statement Caching

Some Oracle client technologies and application frameworks use statement caching.

Statement caching can be beneficial because frequently used statements can be reused rather than repeatedly prepared.

However, the application and DBA should understand how statement caching affects the number of cursors associated with a session.

If ORA-01000 occurs after statement caching is enabled or aggressively configured, review the statement-cache configuration together with actual cursor usage.

Do not disable useful statement caching automatically. First determine whether it is actually contributing to the problem.

Solution 9 – Review Application SQL Generation

Application developers should review whether SQL statements are generated efficiently.

Potential issues include:

Generating unnecessary unique SQL statements.
Repeated hard parsing.
Embedding literals instead of using bind variables where appropriate.
Creating unnecessary prepared statements.
Failing to close result sets.
Failing to close statements.
Keeping statements open longer than required.

Solution 10 – Review Custom PL/SQL Packages

In environments with custom PL/SQL packages, review procedures that:

Open explicit cursors.
Use dynamic SQL.
Call other cursor-intensive procedures.
Execute repeated SQL statements in loops.
Maintain long-running sessions.

Pay particular attention to exception handling.

A procedure may correctly close a cursor on the normal execution path while failing to release resources correctly when an exception occurs.

Solution 11 – Oracle E-Business Suite Considerations

For Oracle E-Business Suite environments, identify the exact component generating the affected database session.

Possible sources include:

Oracle Forms.
Concurrent Managers.
Concurrent Programs.
Application Server components.
Custom reports.
Custom interfaces.
Custom PL/SQL packages.

If standard Oracle E-Business Suite functionality is suspected, check the applicable Oracle support documentation and product-specific recommendations for the exact release.

If custom code is involved, review the custom implementation carefully before increasing the database parameter.

Solution 12 – Monitor Cursor Usage After the Change

If OPEN_CURSORS is increased, do not consider the problem solved immediately.

Continue monitoring:

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.module,
       ss.value AS opened_cursors_current
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 cursor usage remains stable below the new limit, the configuration change may have been appropriate.

If cursor usage continues to grow until it approaches the new limit, the underlying problem still exists.

A Practical Production Example

Assume the database has:

OPEN_CURSORS = 300

A specific application session is observed with:

09:00  →  80
09:30  → 125
10:00  → 170
10:30  → 220
11:00  → 270
11:15  → 295

The session eventually encounters:

ORA-01000: maximum open cursors exceeded

The correct DBA response is not simply:

ALTER SYSTEM SET open_cursors = 1000;

Instead:

Identify the session.
Identify the application and module.
Inspect V$OPEN_CURSOR.
Identify repeated SQL statements.
Review application resource management.
Determine why cursor usage continually increases.
Fix the application or PL/SQL problem if a leak exists.
Increase OPEN_CURSORS only if the legitimate workload requires additional capacity.

Production-Safe Troubleshooting Procedure

A practical production procedure is:

Confirm the error.
Verify that the application is actually receiving ORA-01000.
Check the parameter.
Record the current OPEN_CURSORS value.
Find affected sessions.
Use V$SESSTAT and V$STATNAME.
Identify the application.
Check machine, program, module, action and client identifier.
Inspect cursors.
Use V$OPEN_CURSOR.
Inspect SQL.
Use V$SQL and the relevant SQL_ID.
Check the trend.
Determine whether current cursor usage is increasing over time.
Investigate application code.
Check statement, result-set and connection lifecycle.
Evaluate configuration.
Determine whether the workload genuinely requires more open cursors.
Apply the smallest appropriate fix.
Correct the leak or increase the parameter when justified.
Monitor after the change.
Confirm that the problem does not return.

Should You Restart the Database?

A database restart is generally not the first solution for ORA-01000.

If the problem is caused by a particular application session, terminating or restarting the affected application component may temporarily release its session resources.

Restarting the entire database is usually unnecessary and can introduce significant business impact.

Always diagnose the problem first.

Should You Kill the Affected Session?

Killing an affected session can release its resources, but it is normally an operational workaround rather than a root-cause solution.

Before terminating a production session, determine:

Which user owns the session.
What transaction is running.
Whether uncommitted work exists.
Whether the session belongs to a critical application.
Whether terminating it could cause application or business impact.

Coordinate with the application or operations team when required.

Common Mistakes When Fixing ORA-01000

Mistake 1 – Blindly Increasing OPEN_CURSORS

Increasing the parameter without understanding the cause can hide an application defect.

Mistake 2 – Assuming High Cumulative Cursors Mean a Leak

A high cumulative cursor count can be completely normal for a busy application.

Mistake 3 – Looking Only at the Current SQL

The SQL currently executing is not necessarily responsible for all open cursors.

Mistake 4 – Restarting the Application and Stopping There

A restart may release the affected sessions but does not fix the underlying problem.

Mistake 5 – Ignoring Connection Pools

Persistent pooled sessions can expose resource-management problems that are not visible in short-lived connections.

Mistake 6 – Ignoring Custom PL/SQL

Custom packages, reports and interfaces can introduce cursor-management problems.

ORA-01000 Troubleshooting Matrix

Observation	Possible Cause	Recommended Action
One session reaches the limit	Application or module-specific cursor accumulation	Investigate the session and application code
Many sessions consistently reach a high level	Application legitimately requires more cursors	Evaluate workload and consider increasing `OPEN_CURSORS`
Cursor count continually increases	Possible cursor/resource leak	Review application and PL/SQL resource management
Restart temporarily fixes problem	Session resources are released on restart	Investigate why resources accumulate
Only one application server is affected	Application-server or pool-specific issue	Compare configuration with healthy servers
Many unique SQL statements appear	Possible inefficient dynamic SQL	Review SQL generation and bind-variable usage
Custom package triggers problem	PL/SQL cursor/resource management	Review package and exception-handling paths

Best Practices to Prevent ORA-01000

Monitor current open-cursor usage for important application sessions.
Do not configure an arbitrarily large OPEN_CURSORS value.
Close explicitly opened cursors appropriately.
Release application statement and result-set resources.
Use bind variables where appropriate.
Review dynamic SQL generation.
Monitor long-lived sessions.
Review connection-pool configuration.
Test application changes under realistic workloads.
Monitor cursor usage after parameter changes.
Investigate recurring ORA-01000 instead of repeatedly restarting applications.
Document production parameter changes.

Recommended DBA Monitoring Query

The following query can be retained as part of a DBA diagnostic toolkit:

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.module,
       s.action,
       ss.value AS opened_cursors_current
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;

For recurring incidents, consider integrating cursor-usage monitoring into your existing Oracle monitoring platform.

Quick Solution Checklist

When ORA-01000 occurs:

☐ Check OPEN_CURSORS.
☐ Find sessions with high opened cursors current.
☐ Identify SID and SERIAL#.
☐ Identify the application, machine and module.
☐ Check V$OPEN_CURSOR.
☐ Identify suspicious SQL_IDs.
☐ Review V$SQL.
☐ Check whether cursor usage increases over time.
☐ Investigate application resource management.
☐ Review PL/SQL cursor handling.
☐ Review connection pooling and statement caching.
☐ Increase OPEN_CURSORS only when justified.
☐ Monitor the environment after applying the fix.

Frequently Asked Questions

1. What does ORA-01000 mean?

It means that a database session has exceeded the number of simultaneously open cursors allowed by the OPEN_CURSORS parameter.

2. Is OPEN_CURSORS a database-wide limit?

No. The parameter defines the maximum number of cursors that a session can have open simultaneously.

3. Should I increase OPEN_CURSORS immediately?

Not necessarily. First determine whether the application has a legitimate requirement for more cursors or whether cursor usage is increasing because of a resource-management problem.

4. Can increasing OPEN_CURSORS permanently fix ORA-01000?

It can resolve the error when the configured limit is genuinely too low for the workload. It will not permanently fix an application cursor leak.

5. Why does restarting the application sometimes fix ORA-01000?

Restarting the application disconnects its database sessions. This releases session resources and resets cursor usage for newly created sessions. If the underlying cause remains, the error can return.

6. Which view should I use to investigate open cursors?

V$OPEN_CURSOR is an important diagnostic view. It should be used together with V$SESSION, V$SESSTAT, V$STATNAME, and V$SQL.

7. Does a high cumulative cursor count mean there is a cursor leak?

No. A busy application can legitimately open and close a large number of cursors over its lifetime. Current cursor usage and its trend are more useful for identifying cursor accumulation.

8. Can bind variables prevent ORA-01000?

Bind variables can reduce unnecessary SQL variations and parsing, but they do not replace proper cursor and statement resource management.

9. Can Oracle E-Business Suite encounter ORA-01000?

Yes. The investigation should identify the specific E-Business Suite component, custom code, report, interface, or application session responsible for the excessive cursor usage.

10. Is killing the session a permanent fix?

No. Killing a session can release its resources and restore service temporarily, but the underlying application or configuration problem should still be investigated.

Final DBA Checklist

When troubleshooting ORA-01000: maximum open cursors exceeded, use the following checklist:

Confirm the ORA-01000 error.
Check the current OPEN_CURSORS value.
Find sessions with high current cursor usage.
Identify the affected SID and SERIAL#.
Identify the machine, program, module and action.
Review V$OPEN_CURSOR.
Group cursor information by SQL_ID and cursor type where useful.
Inspect suspicious SQL using V$SQL.
Compare current and cumulative cursor statistics.
Monitor the session over time.
Determine whether cursor usage is stable or continuously increasing.
Investigate application statement and result-set handling.
Review explicit PL/SQL cursor management.
Review dynamic SQL and bind-variable usage.
Review connection-pool and statement-cache configuration.
Investigate Oracle E-Business Suite customizations when applicable.
Fix the root cause when a cursor leak is identified.
Increase OPEN_CURSORS only when justified by actual workload.
Monitor the database after the change.
Document the final root cause and corrective action.

Conclusion

ORA-01000: maximum open cursors exceeded is not simply a parameter error. It is a symptom that a database session has reached its configured open-cursor capacity.

The most effective troubleshooting approach is to identify the affected session, examine its current cursor usage, determine which application or module is responsible, inspect the associated cursors and SQL, and establish whether cursor usage is normal or continuously increasing.

If the application legitimately requires more simultaneously open cursors, increasing OPEN_CURSORS may be the appropriate solution.

If cursor usage continues to grow because resources are not being released, the correct solution is to fix the application, PL/SQL, connection-pool, or resource-management problem.

Key DBA Takeaway

Do not treat ORA-01000 as a number that simply needs to be increased. First determine why the session needs so many open cursors. Correct the underlying resource-management problem whenever possible, and use OPEN_CURSORS sizing to provide appropriate capacity for legitimate workload requirements.

About the Author

Rana Abdul Wahid is a seasoned Oracle DBA Consultant with more than 15 years of Oracle Database experience. His expertise includes Oracle Database Administration, Oracle E-Business Suite Application DBA, Oracle OCI Cloud DBA, MySQL, Microsoft SQL Server, PostgreSQL, Odoo ERP, and Linux/Unix/Ubuntu/Windows administration.

His technical articles focus on practical Oracle Database, Oracle E-Business Suite, Linux, troubleshooting, administration, backup and recovery, performance, and enterprise infrastructure solutions.

Learn more about the author →

Disclaimer: Oracle, Oracle Database, Oracle E-Business Suite, SQL*Plus, and related product names are trademarks of Oracle Corporation. Oracle configuration, compatibility, and migration procedures vary by release. Always consult the applicable Oracle documentation and My Oracle Support information for your exact environment before making production changes.

Oracle Database Error Solutions & DBA Knowledge Base