Tune OPEN_CURSORS parameter in Oracle
Open Cursors parameter is define the maximum open cursor a session can have open.
If you define the value of open_cursors parameter to 300 then a session can open max upto 300 cursors.
Check the value of Open Cursors parameter
Default value assigned is 300
SQL> show parameter open_cursors
NAME TYPE VALUE ------------------- ----------- ------- open_cursors integer 300
Check the maximum value used by Oracle for Setting the OPEN_CURSORS parameter
Col Session_Max_open_cursor for 99999
col Parameter_Value_define for a25
select max(a.value) as Session_Max_open_cursor, p.value as Parameter_Value_define
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;
Note: If value is reached to maximum limit then you can increase the value of this parameter.
Check the usage in percentage of open_cursor
select 'open_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from (select max(s.value) used
from sys.v_$statname n, sys.v_$sesstat s
where n.name = 'opened cursors current'
and s.statistic# = n.statistic#),
(select value
from sys.v_$parameter
where name = 'open_cursors')
Note: If usage is above 95% percent than you can also increase the value.
Increase or Decrease the value by setting with alter command
Change this parameter need to restart the Database.
ALTER SYSTEM SET OPEN_CURSORS =500 scope=BOTH;