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
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
