ORA-01000 and OPEN_CURSOR

What is OPEN_CURSOR?

Ans) OPEN_CURSOR means the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.(Range 0 to 65535)

Error: 

ora-01000: maximum open cursors exceeded

• Monitoring script which provide you full SQLTEXT,USER_NAME,CURSOR_TYPE,SID,Machine, Program information,count

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,s.machine,s.PROGRAM,c.cursor_type

from v$open_cursor c, v$sql sql, v$session s

where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL#;  

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,count(*) as "OPEN CURSORS VALUE",s.machine,s.PROGRAM,c.cursor_type

from v$open_cursor c, v$sql sql, v$session s

where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL# order by count(*) desc;

• Monitoring Script which provide you open_cursor value with SID & Serial# value

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

• To know value about current highest open cursor and 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;

Solution:

1) check application code and check code and check why cursor are staying open? And why its required this much number of open cursor?  

2) increase open_cursor parameter and restart db.(if its required last option)

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;