How to update cursor limit for ORA-01000 maximum open cursors exceed

Question

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

User · Answer

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,

http://www.orafaq.com/node/5

User · Answer

you can update the setting under init ora in oraclexe app oracle product 11 2 0 server config scripts

User · Answer

Assuming that you are using a spfile to start the database  alter system set open cursors   1000 scope both    If you are using a pfile instead  you can change the setting for the running instance  alter system set open cursors   1000    You would also then need to edit the parameter file to specify the new open cursors setting   It would generally be a good idea to restart the database shortly thereafter to make sure that the parameter file change works as expected  it s highly annoying to discover months later the next time that you reboot the database that some parameter file change than no one remembers wasn t done correctly    I m also hoping that you are certain that you actually need more than 300 open cursors per session   A large fraction of the time  people that are adjusting this setting actually have a cursor leak and they are simply trying to paper over the bug rather than addressing the root cause

[oracle11g] How to update cursor limit for ORA-01000: maximum open cursors exceed

Examples related to oracle11g