ORA-01000: maximum open cursors exceeded

You get this error when a user of a host program attempts to open more cursors than they are allowed.

The number of cursors allowed is dictated by the OPEN_CURSORS initialization parameter, and this quota can be eaten up by both implicit and explicit cursors.

However, it is possible that the OPEN_CURSORS number is just too low for the needs of your application and has to be upped. The default value is 50, however the only factor limiting how high this number can go – 300, 1000, 2000 even – is what the operating system can take.

However, it may be unwise to choose to change the OPEN_CURSORS parameter too steeply, rather than examine your code for leaks.

Below are the steps :-

SQL> alter system set open_cursors=1000 (any higher value) scope=both sid='*';

or

If you want to use initi.ora file, then you can create one using spfile or if it already exists you can modify it

 To create pfile

Login as sysdba

 SQL> create pfile='/location/init.ora' from spfile;
File Created;

Cd to location and edit init.ora file

*.open_cursrors=1000 # higher value

sql> shutdown immediate
sql> starutp mount pfile='/location/init.ora';
sql> alter database open;
sql> show parameter open_cursors;