Forum Stats

  • 3,814,139 Users
  • 2,258,824 Discussions
  • 7,892,587 Comments

Discussions

Categories

How to inclease open_cursors in oracle?

670716
670716 Member Posts: 10
edited Dec 10, 2008 1:14AM in General Database Discussions
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.
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • OrionNet
    OrionNet Member Posts: 4,542
    edited Dec 9, 2008 9:47PM 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
    · Share on TwitterShare on Facebook

Answers

  • OrionNet
    OrionNet Member Posts: 4,542
    edited Dec 9, 2008 9:47PM 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
    · Share on TwitterShare on Facebook
  • oradebug
    oradebug Member Posts: 88
    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
    oradebug
    · Share on TwitterShare on Facebook
  • 670716
    670716 Member Posts: 10
    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.
    · Share on TwitterShare on Facebook
This discussion has been closed.