How to resolve the Oracle MAX Connection Limit Exceeded problem on Oracle 10g Express

      Comments Off on How to resolve the Oracle MAX Connection Limit Exceeded problem on Oracle 10g Express
Oracle

Oracle

In the last day of the 2009 i have been called from a company to help them. Its is an Emergency because the old Oracle 9 database is dead and the sales before Christmas and the invoicing system is based on this Database.

So i recovered the database and transfer everything on the new Windows 2008 x64 OS with Oracle 10g Express database. With installation, settings and recovery was 2 hours.

But after some days appears the problems with the processes. Of course it is a wrong documented thirdy party developed ERP software and from my opinion wrong architected. For my surprise the Oracle was speedy but the application is slow and open lot of connection simultaneously from lot of terminal (missing of Singleton Design pattern usage 😀 ).

So the problem is with the limited conenction.

How to verify.

Start the Oracle SQL Console and put the following commands.

SQL> connect as system/"defaultpassword" as SYSDBA;
SQL> show parameter process;

TIPS: generally the “defaultpassword” is “manager
You can see the process parameter is set to 40 from this the oracle use for own process 20 and for applications remains 20. Generally is enough, but in case of wrong software is not.

ora_1

How to resolve?

1. Log on again in the SQL console and put the following commands for increase the process number to 200 by example.

SQL> connect as system/"defaultpassword" as SYSDBA;
SQL> alter system set processes = 200 scope=spfile;

2. Restart the Oracle server

Optional Tip: If do you what to modify the sessions you can do this with following command:

SQL> connect as system/"defaultpassword" as SYSDBA;
SQL> alter system set sessions= 200 scope=spfile;

ora_2

Useful links:

Server Parameters for Oracle 9 and 10g

Oracle 10g Documentation

Spfile and Init.ora Parameter File Startup of an Oracle9i Instance