ORA-01000 maximum open cursors exceeded

When we see this Oracle error

Step 1: First check database and see who is connecting and not closing connections.
Step 2: If connections were kept open for long time means, issue is in code. Check result set and statements where closed properly.
Step 3: Only DBA can alter cursor count if required

Few queries picked from net and useful for quick troubleshooting

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

select sql_text, count(*) as “OPEN CURSORS”, user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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’
group by s.username, s.machine
order by 1 desc;

–Only DBA can do this
ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH;

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;

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s