ORA-01000: maximum open cursors exceeded

Open Cursor is the cursor maximum value used by a session in oracle. If value of Open Cursor is set to 300 then only one session can open maximum 300 cursor at a time when it try to exceed this value then it will generate ORA-01000 error.

Two parameter in oracle:
OPEN_CURSOR
SESSION_CACHED_CURSORS

Both parameter is different in behavior:

Open Cursor parameter is used to set the maximum value of a session can open the cursor.
Session cached cursors is used to set the value of cached cursor in memory for better performance.

IF you want to check which session is utilizing maximum cursor:

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

Solution:

1. Check the value of cursor: highest open and max value

column highest_open_cur for 9999
column max_open_cur for a20
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;

2. Show the open cursors parameter

show parameter open_cursors  #default value is 300.

3. IF Highest value is near by max value then or you got error in alert log. You need to modified the parameter with following command:

ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH;

4. For effect you need the downtime of the oracle database:

shutdown immediate;
startup

5. Verified again the open cursors parameter

show parameter open_cursors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.