Friday, January 22, 2016

ORA-01000: maximum open cursors exceeded

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits.
$sqlplus sboda as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 22 22:55:38 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 256
SQL> show parameter open_cursors
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
open_cursors                         integer                          300
Find out the session which is causing ORA-01000: maximum open cursors exceeded  using Below Sql
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;
Now one can see which all queries are causing maxing out of open cursors using below Sql:

select  sid ,sql_text, user_name, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (SID) group by sid ,sql_text, user_name
Now check which session opens to many cursors?
SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=<sid_number>;
Check the status of the cursor,if it is INACTIVE we can we kill the session by using the below command :
SQL> alter system kill session 'sid,serial#' immediate;
The other alternatives is to increase the no. of the open_cursors parameter as
SQL> alter system set open_cursors=1500 scope=spfile;
In my case i have increased the values of the open_cursors and issue got solved.

Another useful query - total cursors open, by username & machine
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;

No comments: