8 Replies Latest reply: Nov 20, 2012 4:02 AM by Madhu.149 RSS

    Reg: Sessions, processes and transactions

    Madhu.149
      Hello All,

      Oracle database 11g Expression Edition 11.2.0.2

      I want to change the the number of processes to 150 (default is 100)
      I did this by issuing the below command.
      alter system set processes=150 scope=spfile;
      But why that modified value is not being reflected?
      select name, value from v$parameter where name in ('sessions','processes','transactions');
      
      NAME                 VALUE
      -------------------- -----
      processes            100
      sessions             172
      transactions         189
        • 1. Re: Reg: Sessions, processes and transactions
          Aman....
          Works fine for Enterprise Edition , may be a hard limit in XE?
          SQL> select * from V$version;
          
          BANNER
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          PL/SQL Release 11.2.0.1.0 - Production
          CORE    11.2.0.1.0      Production
          TNS for Linux: Version 11.2.0.1.0 - Production
          NLSRTL Version 11.2.0.1.0 - Production
          
          SQL> show parameter process
          
          NAME                                 TYPE        VALUE
          aq_tm_processes                      integer     0
          cell_offload_processing              boolean     TRUE
          db_writer_processes                  integer     1
          gcs_server_processes                 integer     0
          global_txn_processes                 integer     1
          job_queue_processes                  integer     1000
          log_archive_max_processes            integer     4
          processes                            integer     150
          SQL> alter system set processes=200 scope=spfile;
          
          System altered.
          
          SQL> startup force
          ORACLE instance started.
          
          Total System Global Area  418484224 bytes
          Fixed Size                  1336932 bytes
          Variable Size             327158172 bytes
          Database Buffers           83886080 bytes
          Redo Buffers                6103040 bytes
          Database mounted.
          show parameter processDatabase opened.
          SQL> 
          
          NAME                                 TYPE        VALUE
          aq_tm_processes                      integer     0
          cell_offload_processing              boolean     TRUE
          db_writer_processes                  integer     1
          gcs_server_processes                 integer     0
          global_txn_processes                 integer     1
          job_queue_processes                  integer     1000
          log_archive_max_processes            integer     4
          processes                            integer     200
          SQL> 
          Aman....
          • 2. Re: Reg: Sessions, processes and transactions
            Niket Kumar
            have you restarted the database?
            you need a database bounce after changing process number.
            • 3. Re: Reg: Sessions, processes and transactions
              Helios-GunesEROL
              Hi;

              Already Aman workshop should answer you ;)

              Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)

              Refer oracle documentation : http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2087.htm#REFRN30176

              Regard
              Helios
              • 4. Re: Reg: Sessions, processes and transactions
                Karan Kukreja
                Hi ,

                Since you just mentioned scope=spfile , the new number of altered processes would reflect only after a DB bounce. You can see the same as stated in the example by Aman Sir.

                Regards
                KK
                • 5. Re: Reg: Sessions, processes and transactions
                  Madhu.149
                  Thank you...

                  I checked, for oracle 11g Express Edition 11.2.0.2
                  select name, value, isses_modifiable from v$parameter where name in ('sessions','processes','transactions');
                  
                  
                  NAME                 VALUE ISSES_MODIFIABLE
                  -------------------- ----- -----
                  processes            100   FALSE
                  sessions             172   FALSE
                  transactions         189   FALSE
                  Edited by: Madhu.149 on Nov 20, 2012 3:17 PM
                  • 6. Re: Reg: Sessions, processes and transactions
                    Madhu.149
                    I tried this.. but not working.
                    • 7. Re: Reg: Sessions, processes and transactions
                      Helios-GunesEROL
                      Hi again;

                      As you can see some parameters can change dynamicly some others can be alter but change after reboot database, for can understand which parameters can be change dynamily or not is v$view.

                      PS:Please dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding

                      Regard
                      Helios
                      • 8. Re: Reg: Sessions, processes and transactions
                        Madhu.149
                        Hi,

                        Thank you..

                        The value is now being reflected, after shutdown and startup of database.
                        SQL> alter system set processes=80 scope=spfile;
                        
                        SQL> shutdown immediate
                        Database closed.
                        Database dismounted.
                        ORACLE instance shut down.
                        
                        SQL> startup
                        ORACLE instance started.
                        
                        Total System Global Area  644468736 bytes
                        Fixed Size                  1385488 bytes
                        Variable Size             322964464 bytes
                        Database Buffers          314572800 bytes
                        Redo Buffers                5545984 bytes
                        Database mounted.
                        Database opened.
                        
                        SQL> select name, value, isses_modifiable from v$parameter where name in ('sessions','processes','transactions');
                        
                        NAME                 VALUE ISSES
                        -------------------- ----- -----
                        processes            80    FALSE
                        sessions             144   FALSE
                        transactions         158   FALSE