• Increasing Processes, Sessions and Transactions in Oracle XE

    by  • June 22, 2009 • 20 Comments

    Disclaimer: following my technical advice has been known to scratch cars, void lottery tickets and confuse guide dogs – proceed at your own risk..

    Oracle Express Edition (XE)

    Out of the box, Oracle Datatbase 10g Express Edition RDBMS is fast and powerful. The stated limitations of 2GB of maximum RAM usage and 2GB of total datafile management are plentiful for it to easily run as the back-end for a small to medium-sized office application. However, we soon hit a connection limit as characterised by the following Oracle Errors:

    ORA-12516: TNS:listener could not find available handler with matching protocol stack

    ORA-00020: maximum number of processes (%s) exceeded

    ORA-00020: maximum number of processes (%s) exceeded

    We can get this second message because Oracle creates Operating System processes to handle Connections (or Sessions) – which means Processes, Sessions (and as we’ll soon see..) Transactions are all related.

    The default values in Oracle XE for these parameters are:

    • Processes =40
    • Sessions = 49
    • Transactions = 53

    I was able to generate the above error message (ORA-00020) from about ~30 connections on a vanilla Oracle XE installation (on Windows 7).

    So, let’s increase these limits to allow more connections to our Oracle Server..

    1. Log in as SYSDBA

    From the menu ‘Oracle Database 10g Express Edition’, find and select ‘Run SQL Command Line’, then type:

    connect sys as sysdba

     

    and enter your SYS, or SYSTEM password at the prompt

    oraclexe-default-processes-sessions-transactions

    2. ALTER SYSTEM commands

    The Oracle 10g Documentation states that TRANSACTIONS is derived from SESSIONS, which in turn is derived from PROCESSES, thus:

    PROCESSES = 40 to Operating System Dependant

    SESSIONS = (1.1 * PROCESSES) + 5

    TRANSACTIONS = 1.1 * SESSIONS

    So, what to start with for PROCESSES?  Trebling it is as good a start as any, then I’d add a few more for good measure..  Here are the values I recommend:

    • PROCESSES = 150
    • SESSIONS = 300
    • TRANSACTIONS = 330

    type the following commands:

     

    alter system set processes = 150 scope = spfile;

alter system set sessions = 300 scope = spfile;

alter system set transactions = 330 scope = spfile;

     

    then to make the settings take effect, we need to bounce the database..

    shutdown immediate;

startup;

     

    oraclexe-alter-system-commands

    3. Verify the new parameters

    with this simple select statement..

    select name, value
from v$parameter
where name in ('processes', 'sessions', 'transactions');

     

    oraclexe-updated-processes-sessions-transactions

    And we’re done – a free, light and powerful Oracle RDBMS that’s able to serve more connections

    About Andrew Freemantle

    20 Responses to Increasing Processes, Sessions and Transactions in Oracle XE

    1. Nicolas
      January 26, 2010 at 4:19 pm

      Thanks, this is straightforward and working !
      Now up and running with parallel unit tests, still failing, but for good reasons !

    2. Hassene
      February 11, 2010 at 8:53 am

      thanks a lot

      you really help me

      but are this values the max we can choose for oracle express ?

      processes = 150

      sessions = 300

      transactions = 330

      !

    3. Andrew Freemantle
      February 11, 2010 at 8:33 pm

      Hi Hassene, I’m glad it helped you :)

      While there will be an answer to your question of what are the maximum values for Oracle Express, there are a few key factors I’d need to know first:

      1. What Operating System are you running it on?
      2. Is the Operating System 32bit or 64bit (although Oracle XE is 32bit only, this still affects how many processes the OS can handle in total)
      3. How much RAM is available to the Operating System

    4. Christoph Jahn
      February 15, 2010 at 4:25 am

      Thanks for this. Whenever I need to install XE, I go to this site afterwards to change the settings (fatlemon is so easy to remember :-) )

    5. Kxxx78
      February 18, 2010 at 3:15 pm

      Thank you very much, you save me today :) !

    6. Sram
      March 19, 2010 at 2:03 pm

      Hi Andrew,

      I altered the sessions to 150, but everytime I restart the xe database it defaults to 49. Do I have to configure the pfile manually if I want to store it permanently?

      Thanks

    7. Andrew Freemantle
      March 19, 2010 at 10:28 pm

      @Sram – you shouldn’t have to edit the pfile manually to get these values to save permanently – that’s why we’re using the ‘scope’ argument.

      Are you logging in as sysdba?
      Are you including the ‘scope = spfile’ on the alter system commands?

    8. Eddie Hapson
      May 17, 2010 at 3:09 am

      Wow! this info saves me. Thank you so much Sir Andrew for this guide…it really helps me a lot…

    9. Naveen Beniwal
      October 29, 2010 at 3:13 pm

      Thanks a lot !

      The step are simple to understand and helped me a lot.

      Good Work !!

    10. Apoorva
      December 2, 2010 at 3:39 am

      Thank You very much, Andrew..! U r a savior.. : )

    11. Ocky
      April 19, 2011 at 7:31 am

      Wah, keren banget mas. Infonya bagus banget! Makasih banyak!

    12. Alexandr
      May 3, 2011 at 9:49 am

      Thanks a lot!
      This article is really helpful for me :)

    13. Dinesh Ranganathan`
      July 21, 2011 at 11:05 am

      Thanks a lot!
      It works!!!

    14. sutanu.nitdgp@gmail.com
      November 17, 2011 at 4:55 am

      Hi Andrew
      Thanks for your valuable tips…. I am facing another problem about the max size of the express database limit (4 GB). Would you please help me in this regard ? Is it possible to increase the database size of oracle express database ?

    15. Andrew Freemantle
      November 17, 2011 at 2:31 pm

      Hi Sutanu,
      The 4GB data limit is a fixed limit for Oracle Express, and cannot be changed.

      Your upgrade path is to Oracle Standard Edition One, which has a minimum 5 user license and costs GBP £737.70 (about USD $1,170). Definitely call Oracle first, because they have new company discounts.

    16. yalkoki@gmail.com
      December 12, 2011 at 10:42 am

      Thank you very much it works fine but
      my database has not started yet?
      what is the reason Andew.

    17. Andrew Freemantle
      December 12, 2011 at 11:38 am

      Thanks @Yalkoki

      Do you have any error messages, or does it look like it’s just sitting there thinking about it?

      It should come back within a couple of minutes. If it doesn’t, try restarting the Oracle services

    18. yalkoki@gmail.com
      December 14, 2011 at 5:39 am

      Thank you Andrew
      After restarting the service it works.

    19. sssvim@gmail.com
      December 28, 2011 at 7:04 am

      Thank you Andrew
      really awesome description keep it …!!!!!!!!!!!

    20. Sivaram
      December 30, 2011 at 12:43 am

      Thanks a Lot !! Andrew

      It worked for me as well.

    Leave a Reply

    Your email address will not be published.

    Your name *

    Your website

    *