9 Replies Latest reply on Jun 25, 2009 4:26 PM by 693016

    How to increase Session

    650575
      Hi,
      I have 10g database. I m getting this error "ORA-00018: maximum number of sessions exceeded".
        • 1. Re: How to increase Session
          558383
          $ oerr ora 00018
          00018, 00000, "maximum number of sessions exceeded"
          // *Cause:  All session state objects are in use.
          *// Action: Increase the value of the SESSIONS initialization parameter.
          • 2. Re: How to increase Session
            Mohammed Mehraj Hussain
            SQL> show parameter sessions
            Increase this session parameter 
            • 3. Re: How to increase Session
              fjfranken
              Mohd Mehraj Hussain wrote:
              SQL> show parameter sessions
              Increase this session parameter 
              By default the value for sessions is depending on the value of processes, like: SESSIONS is 1.1*PROCESSES + 5.
              So increasing sessions may not really help, as you still can run out of available processes.

              If you however would increase the processes parameter, the number of sessions automatically also increases, following the above formula

              Edit: http://www.freelists.org/post/oracle-l/RE-increasing-number-of-sessions-vs-processes

              Good luck
              FJ
              • 4. Re: How to increase Session
                OrionNet
                Hello,


                Login as sysdba
                sqlplus "/as sysdba"
                or
                sqlplus '/as sysdba'
                
                
                sql> show parameter sessions
                sql> show parameter processes
                sql> show parameter transactions
                And if you are increasing sessions parameter you should consider increasing processes and transactions parameter as well. Here is the formula you can use to determine their values.
                processes=x
                sessions=x*1.1+5
                transactions=sessions*1.1
                
                E.g.
                processes=500
                sessions=555
                transactions=610
                
                sql> alter system set processes=500 scope=both sid='*';
                sql> alter system set sessions=555 scope=both sid='*';
                sql> alter system set transactions=610 scope=both sid='*';
                Regards
                • 5. Re: How to increase Session: getting can't be modified Error Message
                  693016
                  Hi All,


                  I need to modify the few initialization parameters in my Oracle 10g database. I am using the oracle DB Console to do this however I am not

                  able to the sessions and processes parameters from it. So, I tried doing it from sql prompt. I executed following query to do it:

                  alter system set processes=360 scope=both sid='';*
                  alter system set sessions=400 scope=both sid='';*

                  but I got the Specified Initialization parameter cannot be modified error message.

                  Is anyone aware of the cause of this error. If yes, then please reply.

                  Beside this, after modifying the rest of the parameters, when I restarted the database instance, all the modified values got revertback :-(. I

                  saved everytime I modify any initialization parameter.

                  Cheers
                  Sunny
                  • 6. Re: How to increase Session: getting can't be modified Error Message
                    Boochi
                    If you are modifying from DB Console, choose the radio button 'SPFile' on the top and then modify and restart the database.
                    • 7. Re: How to increase Session: getting can't be modified Error Message
                      EdStevens
                      sunny@newbie wrote:
                      Hi All,


                      I need to modify the few initialization parameters in my Oracle 10g database. I am using the oracle DB Console to do this however I am not

                      able to the sessions and processes parameters from it. So, I tried doing it from sql prompt. I executed following query to do it:

                      alter system set processes=360 scope=both sid='';*
                      alter system set sessions=400 scope=both sid='';*

                      but I got the Specified Initialization parameter cannot be modified error message.

                      Is anyone aware of the cause of this error. If yes, then please reply.
                      If you'll check these specific parameters in the Reference Manual (tahiti.oracle.com) you'll find they are "not modifiable". That means they can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
                      Beside this, after modifying the rest of the parameters, when I restarted the database instance, all the modified values got revertback :-(. I
                      Since they are not dynamic, and you specified scope=both, the ALTER statement failed. It's not that they reverted, they never got set in the first place.
                      saved everytime I modify any initialization parameter.
                      Depends on if the parameter is dynamic ("modifiable") or not.

                      >
                      Cheers
                      Sunny
                      • 8. Re: How to increase Session: getting can't be modified Error Message
                        693016
                        Hi Boochi,

                        It worked that way. Thanks.

                        Cheers
                        Sunny
                        • 9. Re: How to increase Session: getting can't be modified Error Message
                          693016
                          Hi,

                          Thanks for the reply and explanation.

                          From db console, I modified all the parameters (dynamic and static) both by selecting spfile tab.

                          Cheers
                          Sunny