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