Jai’s Weblog – Tech, Security & Fun…

Tech, Security & Fun…

  • Jaibeer Malik

    Jaibeer Malik
  • View Jaibeer Malik's profile on LinkedIn
  • Subscribe

  • Feedburner

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 29 other followers

  • Archives

  • Categories

  • Stats

    • 379,210
  • Live Traffic

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;

11 Responses to “How to set maximum open cursors in oracle?”

  1. 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

  2. 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

  3. 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

  4. Chandrakanta Sahoo said

    Thank U So much for support…..

  5. asad said

    @Jai:Thanks a lot for your help🙂

  6. 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 …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: