Increasing Processes, Sessions and Transactions in Oracle XE

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

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

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 19c documentation instead.

The Oracle 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 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');

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