Sunday, November 23, 2008

Oracle XE "Processes" Parameter

Oracle XE is an excellent lightweight substitute for the real Oracle database during development. A good database to run on every developer machine.

One of the common issues with XE is that the connection refused exceptions generated when a project test suite consisting of significant number of database tests is run. This is also true for any database intensive, connection pool application running on Oracle XE.

This is primarily due to the XE initialization parameter "processes".

Following is the definition of this parameter as mentioned in the Oracle documentation.


Specifying the Maximum Number of Processes

The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes.

In short the "processes" parameter controls the maximum number of concurrent connections to the database. By default this is set to 50. Increasing this parameter will help in resolution of the "connection refused" issue mentioned above.

Following are the steps to change the parameter.

1. Connect to the database
sqlplus sys as sysdba


2. See the value of the process parameter
show parameter processes

Sample Output

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 50

3. Change the parameter
alter system set processes=500 scope=spfile
500 is a sample value, set it to any appropriate value. spfile represents the server parameters file.

4. Shutdown the database
shutdown immediate

5. Start the database
startup

6. Look at the parameters.
show parameter processes

The processes parameter should have changed to the new value.

Now running the test suite or any database intense application would not cause the connection refused error to be generated.

3 comments:

rmathew said...

Welcome back! :-)

Vasu said...

Hi, I am facing the same issue but my database is 11.1.0.7 Enterprise Edition. I verified the processs also, it set to 500. Getting the same error say "JVM is not installed".

Can you please help me.

Thanks & Regards,

Vasu

qyuz555 said...

Hello!

At 3rd step you have to terminate alter system set processes=500 scope=spfile command with ;

"alter system set processes=500 scope=spfile"
"2 ;"

And it will prompt you that "System altered."