Donald K. Burleson Oracle Tips

Avoiding an Oracle outage with tablespace growth

One of the most common problems in Oracle database is bumping-up against an Oracle database threshold.  This happens commonly on many Oracle databases, even with experienced Oracle DBAs.  This is a very embarrassing and preventable problem because it can be prevented by planning ahead.

The test database functions well, and the database only fails when a large number of users access the database, causing an unplanned database outage.  In order to ensure that this never happens, proactive Oracle professionals will always plan for growth, and set the following OS and Oracle parameters:

Within the operating system, the Oracle professional must make sure that the shared memory on the database server is configured to allow Oracle to consume all of the available RAM on the server.

OS parameters

• SHMMAX - For a large SGA, the UNIX shared memory variable, SHMMAX must be set to 1GB.

• SEMMNS – This control the number of OS semaphores.  The value of SEMMNS should be set to the number of processes that are using Oracle.

 Oracle initialization parameters

• files - This init.ora parameter controls the number of files allowed.  If it is exceeded, and database must be bounced.

• processes - This is the total number of processes allowed within Oracle, and is closely tied to the number of available semaphores

• sessions – This is the total number of sessions that Oracle may accept

The prudent Oracle professional will always set these parameters to a high value to avoid the embarrassing outages that can occur when the database grows.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: