Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to inclease open_cursors in oracle?

670716Dec 9 2008 — edited Dec 10 2008
Hi, someone give me hands,please.

I want to change oracle parameter "open_cursors" from default value of it.
Where is init.ora file in oracle 10g database?

Let me know.
This post has been answered by OrionNet on Dec 9 2008
Jump to Answer

Comments

OrionNet
Answer
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;

Edited by: OrionNet on Dec 9, 2008 9:42 PM
Marked as Answer by 670716 · Sep 27 2020
oradebug
You can't have my hands, and it sounds like you should probably talk to the person who set the system up, if that is an option. This is probably one of the most basic of database administration tasks. If you don't know how to do this, you probaby shouldn't be working on a production system (assuming you are).

Second, you probably want to make sure you really need to do this. The default value is 50 cursors per session. Not for the whole instance, that's for each session. Does your application really need to hold more than 50 cursors open in each database session? Or is it leaking cursors (as I suspect)?

If you truly must change this value, try this while logged into SQL*Plus as SYSDBA:

SQL> alter system set open_cursors=<new_value> scope=both;

If that works, you are using SPFILE, which means that the initialization parameters are dynamically managed.

If that returns "ORA-32001: write to SPFILE requested but no SPFILE specified at startup" then you are using init<sid>.ora files. You may have a file in $ORACLE_HOME/dbs (Linux/Unix) or in %ORACLE_HOME\database (Windows) called init<SID>.ora. If that is present, you can change the value for open_cursors by adding a line to that file or changing any existing setting to look like:

open_cursors=<new_value>

Regards,

Jeremiah Wilton
ORA-600 Consulting
Recovery - Services - Seminars
http://www.ora-600.net
http://oradeblog.blogspot.com
1 person found this helpful
670716
Hello, I appreciate both suggestions and helps.
I really wanted to solve as soon as possible this issue because I have been executing system test and got the error message near the dead line.

I want to thank you very much, again.
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 7 2009
Added on Dec 9 2008
#database, #general-database-discussions
3 comments
44,110 views