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
