Configuring Oracle Express for development

It’s been a rough couple weeks getting used to Oracle, but I’m finally getting my development environment smoothed out. One important configuration change I’ve had to do on two instances of Oracle Express is upping the maximum number of connections the database server will accept. As usual, I came upon the problem by way of a thoroughly useless Oracle error–

ORA-12516: TNS:listener could not find available handler with matching protocol stack

“Of course! Wait… what?” Luckily, those who precede me have encountered the same problem, and the recommend fix (from the Underground PHP and Oracle Manual (PDF), page 175) worked like a charm.

First, connect to Oracle as the SYS user using SQL*Plus — that’s “Run SQL Command Line” for us Windows folk.

SQL> CONNECT SYS/password AS sysdba
Connected.

Then, check the value of your “processes” parameter.

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     40

By default, that last value is 40. I’m not sure why 40 isn’t enough — seems like a lot of connections — but let’s up it to 100.

SQL> alter system set processes=100 scope=spfile;
System altered.

Then, shutdown and restart the server.

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

Total System Global Area  805306368 bytes
Fixed Size                  1289996 bytes
Variable Size             213909748 bytes
Database Buffers          587202560 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.

If you like, you can run that “show parameter processes” command again to confirm that 100 is the new value.

This entry was posted on December 12th, 2007 and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.