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
-
Recent Comments
VISHNU on JSR 286 Wicket Portlet Develop… 
Ahmed Malik on Tips&Tricks: Flex expand A… Ahmed Malik on Tips&Tricks: Flex expand A… 
Assaf on Flex: Enterprise security impl… 
Leonardo Nunes on Flex: Cairngorm View Notificat… 
Ravi on How to set maximum open cursor… 
Jai on Implementing Kanban for Servic… 
Dany M on Implementing Kanban for Servic… Jai on Implementing Kanban for Servic… Dany m on Implementing Kanban for Servic… 
Follow me on FriendFeed
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