ORA-01000: maximum open cursors exceeded

 

The OPEN_CURSORS parameter is set to the maximum number of cursors that each session can open at a time.

Example, if the value of OPEN_CURSORS is set to 2000, then each session can have up to 2000 cursors open at one time.

If the number of cursors in the database exceeds the maximum limit then you will get the following error:

ORA-01000: maximum open cursors exceeded.

Cause : The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors.

compare the parent and child table rows, if both table rows are not matched then you may get this error

further you can check the following steps:

Login to the schema with DBA privilege of the database.

To find out the session that is causing the error using the below SQL statement:

 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#  AND S.SID=A.SID AND B.NAME = 'OPENED CURSORS CURRENT' AND S.USERNAME IS NOT NULL;

NAME                                 TYPE        VALUE

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

open_cursors                         integer     2048

check maximum number of open cursors in oracle?

select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor

group by sql_text, user_name order by count(*) desc;

you will get the output as details of all sessions. You can see the maximum out session IDs.

Connect to database as sysdba user, get current setting of open cursors limit

show parameter open_cursors

 

NAME                                 TYPE        VALUE

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

open_cursors                         integer     1000

list the top 10 sessions which are currently opening most cursors

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

Check what makes session= 93 open so many cursors?

SQL> select sid, status, event, seconds_in_wait, state, blocking_session, prev_sql_id  from v$session where sid=93;

 

 SID STATUS   EVENT                          SECONDS_IN_WAIT STATE               BLOCKING_SESSION PREV_SQL_ID

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

 93 INACTIVE SQL*Net more data from client            19607 WAITING                              uuyzf60cphjs2a

 

&nbsp;

 

SQL> select sql_text from v$sqlarea where sql_id ='uuyzf60cphjs2a'

 

SQL_TEXT

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

begin ro_tst.ctrlrorfder(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11); end;

 

SQL> select * from

(select SQL_TEXT, sql_id, user_name, count(*)

from v$open_cursor where sid=93

group by SQL_TEXT, sql_id, user_name

order by count(*) desc)

where rownum < 11;

 

SQL_TEXT                                                                         SQL_ID        USER_NAME  COUNT(*)

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

check the recent executed package. we find the piece of code that caused remaining open cursors:

Report the  problem to apps  team and kill these sessions INACTIVE exceeding limit of opening cursors.

or 

ALTER SYSTEM SET OPEN_CURSORS = 3500 SCOPE=BOTH;

Check if open cursors reach the limit set by OPEN_CURSORS

SELECT MAX(a.value) as highest_open_cur, 

       p.value as max_open_cur

  FROM V$SESSTAT a, V$STATNAME b, V$PARAMETER p

 WHERE a.statistic# = b.statistic#

   AND b.name = 'opened cursors current'

   AND p.name= 'open_cursors'

GROUP BY p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR

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

             300 400