Check maximum utilization of open cursor in Oracle

Monitor the currently maximum open cursor in Oracle

For error:
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1

Check the session which causes the issue:

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;

Check the Query causing to open many cursors:

select  sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in ($SID);

Check session wise open cursor:

SELECT sid,user_name, COUNT(*) "Cursors per session" FROM v$open_cursor GROUP BY sid,user_name;

Check the maximum current open cursor from all sessions:

select max(Cursorpersession) from (select sid,count(*) cursorpersession from v$open_cursor GROUP BY sid);

Check the currently maximum open cursor:

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;

Check the current value of the open cursor:

SELECT value FROM v$parameter WHERE name = 'open_cursors';

Change the value of open cursor:

alter system set open_cursors = 3000 scope=both;

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.