I have found my max number of cursors per database to be 300 from the following query:
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;
I tried to update the amount to 1000 with this:
update v_$parameter
set value = 1000
where name = 'open_cursors';
But I am seeing this error:
SQL Error: ORA-02030: can only select from fixed tables/views
02030. 00000 - "can only select from fixed tables/views"
*Cause: An attempt is being made to perform an operation other than
a retrieval from a fixed table/view.
*Action: You may only select rows from fixed tables/views.
What is the proper way to update the open_cursor value? Thanks.
This question is related to
oracle11g
you can update the setting under init.ora in oraclexe\app\oracle\product\11.2.0\server\config\scripts
RUn the following query to find if you are running spfile or not:
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
If the result is "SPFILE", then use the following command:
alter system set open_cursors = 4000 scope=both; --4000 is the number of open cursor
if the result is "PFILE", then use the following command:
alter system set open_cursors = 1000 ;
You can read about SPFILE vs PFILE here,
Source: Stackoverflow.com