ORA-01000: Maximum Open Cursors Exceeded

(Source: Neeraj Blog)

Once our client report that they are facing error  “ORA-01000: maximum open cursors exceeded”while  running a application . As it seems from error that the error is related to cursors limits i.e, open cursors are exceeding from it's defaults values. To solving this issue ,let's have a look on the open_cursor i.e, what is open_cursor and how it impact into  database.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 18:05:30 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter open_cursors
NAME                          TYPE          VALUE
-----------------        ---------      -----------
open_cursors              integer            300

Since the no. of open_cursors is 300. So we list the top 10 sessions which are currently opening most cursors

SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

VALUE           NAME                              SID
-----            -----------------                ----------
300         opened cursors current          131
300         opened cursors current          125
300        opened cursors current           143
300         opened cursors current          149
300        opened cursors current           17
300        opened cursors current           132
300        opened cursors current           23
300        opened cursors current           1
300        opened cursors current           9
300         opened cursors current          10
10 rows selected.

Now we check what make session 131 open to many cursors?

SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=131;

SID  STATUS      EVENT                  WAIT(s)  STATE    BLK_SESN    SQL_ID
---   ----------   ------------------ --------     ---------   ---------    ---------------
131 INACTIVE rdbms ipc message   8745     WAITING                 6mqvntr9ytnga


Since the status of the cursor is INACTIVE so we can we kill the session by using the below command :

SQL> alter system kill session 'sid,serial#' immediate;

The other alternatives is to increase the no. of the open_cursors parameter as :

SQL> alter system set open_cursors=1500 scope=spfile;

In my case i have increased the values of the open_cursors and issue got solved.