Increasing Processes, Sessions and Transactions in Oracle XE

Out of the box, Oracle Database 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

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

2. ALTER SYSTEM commands

Update: The Oracle XE 10g documentation links below were broken, so they now point to the Oracle 11g R2 Standard Edition documentation instead - note that the default values are greater in 11g R2 Standard than the Express Edition (XE).

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

PROCESSES = 40 to Operating System Dependant

SESSIONS = (1.1 * PROCESSES) + 5

TRANSACTIONS = 1.1 * SESSIONS

So, what value 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;

3. Verify the new parameters

with this simple select statement..

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