ORA-01000: Maximum Open Cursors Exceeded

Error ORA-01000: Maximum Open Cursors Exceeded

Applies to:

            Oracle Database 11.2.0.4

Description:

We are facing the error “ora-01000: maximum open cursors exceeded” on our production database server. The error is related to cursor limit, open_cursors exceeding its default value.

Solution:

            To fix the issue we can either kill the inactive sessions which have open the large number of cursors or increase the value of open_cursors.

To check the open_cursors limit:

Execute the following query to list the top 10 sessions which are currently opening most cursors:

SQL> select * from(select sess.sid, stan.name, sess.value

from v$sesstat sess, v$statname stan

where sess.statistic# = stan.statistic#

and stan.name like ‘%opened cursors current%’

order by desc 3)

where rownum < 11;

Now we check the detail of above sessions open too many cursors:

SQL> select sid, serial#, event, seconds_in_wait, blocking_session, prev_sql_id

            from v$session where sid=&sid;

We can kill the session having inactive status by using the below command:

SQL> alter system kill session ‘sid,serial#’ immediate;

The other way to fix the problem to increase the value of open_cursors parameter as below:

SQL> alter system set open_cursors=1000 scope=both;

It’s working fine now.

           

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

You are requested to follow my Blog