Oracle ORA-01000 maximum open cursors exceeded: find the leak and fix it

#Symptom

The application logs show one of these, usually after it has been running for hours or days:

ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors for session exceeded

The exact text varies by release. Recent releases can also name a potentially leaked SQL_ID in the error text, so read the whole message.

OPEN_CURSORS is a per-session limit. One session hitting it doesn't stop the others, but a connection pool often fails one connection after another as each reaches the limit. A restart seems to fix it, then the error comes back.

There are two possible causes: the application leaks cursors, or it needs more open cursors than the limit allows. Find out which before you change anything.

#Checks

#1. See which sessions are closest to the limit

SELECT s.sid, s.serial#, s.username, s.status, s.program, s.module, s.machine,
       s.logon_time, c.value AS open_cursors_now, p.lim AS open_cursors_limit,
       ROUND(100 * c.value / p.lim, 1) AS pct_of_limit
FROM   v$sesstat c
       JOIN v$statname n ON n.statistic# = c.statistic#
       JOIN v$session  s ON s.sid = c.sid
       CROSS JOIN (SELECT TO_NUMBER(value) AS lim
                   FROM v$parameter WHERE name = 'open_cursors') p
WHERE  n.name = 'opened cursors current'
ORDER  BY c.value DESC
FETCH  FIRST 20 ROWS ONLY;

On RAC, run it on each instance. In a multitenant database, connect to the affected PDB.

#2. See whether the count keeps growing

Group by application and run this every few minutes during normal load:

SELECT SYSTIMESTAMP AS sampled_at, s.program, s.module, s.machine,
       COUNT(*) AS sessions, SUM(c.value) AS total_open, MAX(c.value) AS max_open
FROM   v$sesstat c
       JOIN v$statname n ON n.statistic# = c.statistic#
       JOIN v$session  s ON s.sid = c.sid
WHERE  n.name = 'opened cursors current'
AND    s.type = 'USER'
GROUP  BY s.program, s.module, s.machine
ORDER  BY max_open DESC;

A count that climbs steadily and doesn't fall when the application goes quiet points to a leak. A count that rises with load and levels off points to a limit that is too low, or a statement cache that is too large.

#3. See which SQL holds the cursors in one session

Use a SID from check 1:

SELECT sql_id, cursor_type, COUNT(*) AS cursors, MAX(sql_text) AS sql_text_start,
       MAX(last_sql_active_time) AS last_active
FROM   v$open_cursor
WHERE  sid = :sid
AND    cursor_type IN ('OPEN', 'OPEN-RECURSIVE', 'OPEN PL/SQL')
GROUP  BY sql_id, cursor_type
ORDER  BY cursors DESC;

V$OPEN_CURSOR also lists cached cursors, such as SESSION CURSOR CACHED and PL/SQL CURSOR CACHED, so an unfiltered count can mislead you. Cached entries are not evidence of a leak. Use opened cursors current from check 1 to measure the session against the limit, and this filtered query to see what is open.

SQL_TEXT holds only the first 60 characters. Get the full statement with:

SELECT sql_fulltext FROM v$sql WHERE sql_id = :sql_id AND ROWNUM = 1;

#4. Read the pattern

#5. Catch the failing statement if you still can't see it

If the session dies before you can query it, have the database write a trace when the error fires:

ALTER SYSTEM SET EVENTS '1000 trace name errorstack level 3';
-- wait for the error, read the trace file in the diag trace directory, then:
ALTER SYSTEM SET EVENTS '1000 trace name errorstack off';

Warning

This writes a trace file for every ORA-01000 on the instance. If the error is firing constantly, it can generate a lot of trace files quickly. Leave it on only until you have a few, and check that the diagnostic destination has free space.

#Fix

#Get the application working again

The quickest safe action is usually to recycle the pool or restart the affected application instance. That closes its sessions and their cursors. If only one session is the problem, you can kill it:

ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;
-- on RAC: ALTER SYSTEM KILL SESSION '<sid>,<serial#>,@<inst_id>' IMMEDIATE;

On Amazon RDS for Oracle, use the supplied procedure:

EXEC rdsadmin.rdsadmin_util.kill(sid => <sid>, serial => <serial#>, method => 'IMMEDIATE');

Caution

Killing a session rolls back its open transaction, and the application sees an error on that connection. Check s.status, s.program and any open transaction in V$TRANSACTION first. A large rollback can take a long time.

#Raise OPEN_CURSORS when the need is real, or to buy time

OPEN_CURSORS can be changed with ALTER SYSTEM and doesn't need a restart. It can't be changed with ALTER SESSION. It can be set at PDB level.

SHOW PARAMETER open_cursors

-- Choose a value from the peak you measured, plus headroom.
ALTER SYSTEM SET open_cursors = <new_value> SCOPE = BOTH;
-- on RAC, add: SID = '*'

SCOPE = BOTH requires an SPFILE. Oracle's documentation says a higher value adds no overhead as long as sessions don't actually open that many cursors. A session that does open them uses more memory, so a large value won't save you from a leak.

On Amazon RDS, change it through the parameter group:

aws rds modify-db-parameter-group \
  --db-parameter-group-name <your-parameter-group> \
  --parameters "ParameterName=open_cursors,ParameterValue=<new_value>,ApplyMethod=immediate"

#Fix a leak in the code

Java (JDBC). Use try-with-resources so the ResultSet and PreparedStatement close on every path, including exceptions:

String sql = "SELECT status FROM orders WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, orderId);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            process(rs.getString(1));
        }
    }
}

PL/SQL. Close explicit cursors in the exception handler as well as on the normal path:

DECLARE
  CURSOR c_orders IS SELECT id FROM orders WHERE status = 'NEW';
  v_id orders.id%TYPE;
BEGIN
  OPEN c_orders;
  LOOP
    FETCH c_orders INTO v_id;
    EXIT WHEN c_orders%NOTFOUND;
    process_order(v_id);
  END LOOP;
  CLOSE c_orders;
EXCEPTION
  WHEN OTHERS THEN
    IF c_orders%ISOPEN THEN
      CLOSE c_orders;
    END IF;
    RAISE;
END;
/

Every DBMS_SQL.OPEN_CURSOR needs a matching DBMS_SQL.CLOSE_CURSOR. When a procedure returns a REF CURSOR, the client must close it.

#Check the statement cache size

A statement cache keeps cursors open on purpose. The cache size is per connection, and it must leave room below OPEN_CURSORS for everything else the session opens, including recursive SQL. Setting names vary by driver and pool, so check your pool's documentation.

#Verify

1. Rerun check 2 over a full load cycle. max_open should level off and stay below the limit.
2. Rerun check 3 for a busy session. No SQL_ID should keep growing.
3. Search the application logs for ORA-01000 since the fix.

#Prevent it next time

• Add the check 1 query to your monitoring, and alert when pct_of_limit passes a level that gives you time to act.
• Graph max_open by application, so a slow leak shows up before the limit does.
• Add a soak test to releases and compare opened cursors current at the start and end.
• Require try-with-resources or context managers in code review.
• Set OPEN_CURSORS from measured peaks, and write down why any increase was made.