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.
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