3 min read

I meant to post this ages ago but forgot until I came across the issue again today.

When developing a system which runs against an Oracle Database, Oracle XE can be very useful. XE is a cut down version of Oracle’s standard edition Database and is available completely free for development purposes. It is built from the same code base as the standard edition Database, albeit with a very restricted configuration to stop people deploying it in production in lieu of a paid for version.

One of the restrictions in XE is that the number of concurrent sessions and processes that the Database will allow is set to a very low figure, again presumably to deter from production use. This can result in issues during development, whereby seemingly innocuous tasks such as running automated test suites will cause the database to hit the default limits and fail.

The errors that are raised when these limits are reached are often apparently unrelated to the task being performed. Once learned however these errors are easy to spot, this is one reason why it’s useful to know about this default limitation of XE. Below are some of the errors which can manifest from Oracle running out of sessions/ processes:

ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (%s)
TNS Name Not Found

Thankfully the solution to this problem is simple: Increase the number of sessions and processes that the Database instance allows. Here’s how you do it:

1: Log on to the Database with a sysdba account.

sqlplus system@XE as sysdba

2: Check the current session and processes limits

SQL> show parameter session

NAME                                 TYPE        VALUE
———————————— ———– ——————————
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     20
shared_server_sessions               integer

SQL> show parameter processes

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     4
log_archive_max_processes            integer     2
processes                            integer     20

3: Increase the session and processes limits to something sensible, the important thing here is to choose values which solve your particular issue. I tend to find that anything around 250 is fine for most development projects, but remember that the higher the values the more resources Oracle will try and consume when it needs to maintain those extra sessions and processes.

SQL> alter system set sessions=300 scope=spfile;

System altered.

SQL> alter system set processes=300 scope=spfile;

System altered.

4: Shutdown the Database instance.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

5: Restart the Database instance.

I don’t know why but issuing the startup command doesn’t work for me when using XE. I think this may have something to do with the Oracle Listener not being able to identify the instance once it has stopped but I haven’t yet figured it out. Instead I have to restart the Oracle service/ daemon using the following command:

C:\>net start OracleServiceXE
The OracleServiceXE service is starting…..
The OracleServiceXE service was started successfully.

Note: The above is obviously a windows example, I believe that there is usually an /etc/init.d script which has the same effect under a Linux installation.

6: Close the SQLPLUS session and open a new one (otherwise you will receive an error when entering commands as the session cannot persist across the Database restart).

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\development\stsworkspace\compliance>sqlplus system@XE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 20 17:13:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

7: Check that the new values have been persisted across the restart and are now in effect.

SQL> show parameter session

NAME                                 TYPE        VALUE
———————————— ———– ——————————
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     335
shared_server_sessions               integer
SQL> show parameter processes

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     4
log_archive_max_processes            integer     2
processes                            integer     300

Note: I’m not yet sure why but my sessions value always seems to be higher than the value I set. Regardless the value should be different from the initial value and close to the one you set (in my case it is usually above by a value of 15 or 35!)

et voila! That should do it.

Hope that helps someone,

EdD