Oracle12c: Resolving Open Cursor Errors

Problem

You are frequently getting the Maximum Open Cursors exceeded error, and you want to resolve the error.

Solution

One of the first things you need to do when you receive the ORA-01000: “maximum open cursors

exceeded” error is to check the value of the initialization parameter open_cursors. You can view the

current limit for open cursors by issuing the following command:

SQL> sho parameter open_cursors

NAME TYPE VALUE

------------------------------------ ----------- ---------

open_cursors integer 300

The parameter OPEN_CURSORS sets the maximum number of cursors a session can have open at once.

You specify this parameter to control the number of open cursors. Keeping the parameter’s value too

low will result in a session receiving the ORA-01000 error. There’s no harm in specifying a very large value for the OPEN_CURSORS parameter (unless you expect all sessions to simultaneously max out their cursors, which is unlikely), so you can usually resolve cursor-related errors simply by raising the parameter value to a large number. However, you may sometimes find that raising the value of the open_cursors parameter doesn’t “fix” the problem. In such cases, investigate which processes are using the open cursors by issuing the following query:

SQL> select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id

from gv$sesstat a,gv$statname b,gv$session s

where a.statistic# = b.statistic# and s.sid=a.sid

and b.name='opened cursors current'

The GV$OPEN_CURSOR (or the V$OPEN_CURSOR) view shows all the cursors that each user session has

currently opened and parsed, or cached. You can issue the following query to identify the sessions with a

high number of opened and parsed or cached cursors.

SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text

from gv$open_cursor  where sid in (select sid from v$open_cursor

group by sid having count(*) > &threshold);

The query lists all sessions with an open cursor count greater than the threshold you specify. This way, you can limit the query’s output and focus just on the sessions that have opened, parsed, or cached a large number of cursors.

You can get the actual SQL code and the open cursor count for a specific session by issuing the following query:

SQl> select sql_id,substr(sql_text,1,50) sql_text, count(*)

from gv$open_cursor where sid=81

group by sql_id,substr(sql_text,1,50)

order by sql_id;

The output shows the SQL code for all open cursors in the session with the SID 81. You can examine all SQL statements with a high open cursor count, to see why the session was keeping a large number of cursors open.

The default value of 50 for the SESSION_CACHED_CURSORS initialization parameter may be too low for

many databases. You can check if the database is bumping against the maximum limit for session cached cursors by issuing the following statement:

SQL> select max(value) from v$sesstat  where statistic# in (select statistic# from v$statname

 where name = 'session cursor cache count');

MAX(VALUE)

----------

49

The query shows the maximum number of session cursors that have been cached in the past. Since this number (49) is virtually the same as the default value (or the value you’ve set) for the SESSION_CACHED_CURSORS parameter, you must set the parameter's value to a larger number. Session cursor caches use the shared pool. If you’re using automatic memory management, there’s nothing for you to do after you reset the SESSION_CACHED_CURSORS parameter—the database will bump up the shared pool size if necessary. You can find out how many cursors each session has in its session cursor cache by

issuing the following query:

SQL> select a.value,s.username,s.sid,s.serial#  from v$sesstat a, v$statname b,v$session s

 where a.statistic#=b.statistic