How to set maximum open cursors in oracle?
Posted by Jai on January 21, 2009
How to set the maximum number of open cursors?
ALTER SYSTEM SET OPEN_CURSORS=300 SCOPE=spfile;
Jai’s Weblog – Tech, Security & Fun…
Tech, Security & Fun…
Jaibeer Malik
Subscribe
Feedburner
Stats
Live Traffic
Books
Agile Project Management with GreenHopper 6 Blueprints
Follow me on FriendFeed- An error has occurred; the feed is probably down. Try again later.
Jai's Weblog - Tech, Security & Fun... 
Ravi Gupta said
Will this set max cursor at the user level or DB level..? i.e 300 is the total limit on DB or for each user 300 cursors are allowed…?
Jai said
Hi Ravi,
You can set this parameter value at instance/database level using SID attribute but not at the user level.
ALTER SYSTEM SET OPEN_CURSORS=3000 SID=’*’ SCOPE=MEMORY;
ALTER SYSTEM SET OPEN_CURSORS=2000 SID=’DBINST1′ SCOPE=MEMORY;
For more details, http://www.praetoriate.com/oracle_tips_mamt_parameter_file.htm
-Jai
Scott said
Hi,
Can you tell me if this chg takes effect immediately or if I have to stop/start the Oracle DB instance.
Thx.
Scott
Jai said
Please refer the above URL,
SCOPE = SPFILE
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)
SCOPE = MEMORY
(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)
SCOPE = BOTH
(For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
Examples:
The following statement affects all instances. However, the values are only effective for the current instances, they are not written to binary SPFILE.
ALTER SYSTEM SET OPEN_CURSORS=2000 SID=’*’ SCOPE=MEMORY;
The next statement resets the value for the instance ‘RACDB1’. At this point, the database-wide setting becomes effective for SID of RACDB1.
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid=’RACDB1′;
Scott said
Hi Jai,
I did read this psot already.
We’re running 10.2.0.4 and my DBA is telling me that we don’t need to restart the DB…
Scott
Ravi said
This article helped me a lot.
The query I executed is as follows
“ALTER SYSTEM SET OPEN_CURSORS=2000 SID=’DUMMY'”;
and it executed succesfully.
When I tried using SCOPE=MEMORY, it didn’t execute and gave some error message.
But I’m happy as my work got done without that also.
Thanks
Ravi
Dilip H P said
Hi,
I have set the max cursor limit to zero for one of our db.. now it’s saying that no connections are possible because cursor cannot be opened. This is one of our critical db. Can you please help in resetting the parameter for cursor..
Thanks
Dilip
Andy said
You disk Dilip – thanks for the laugh.
Chandrakanta Sahoo said
Thank U So much for support…..
asad said
@Jai:Thanks a lot for your help 🙂
frozanmohammadi5@gmail.com said
how to find the max of numbers in sql plus, the number was static … e.g 1,2,3,4,5,6,7,8 I want that query that find the max of this …