25 August 2010

ORA-00020: maximum number of processes (%s) exceeded

ORA-00020: maximum number of processes (%s) exceeded

This means that you have too many database connections basically. Unfortunately since the demise of svrctl, it also means that you as a DBA cannot connect internal to do something about it!

You need to either ask a couple of users to log out, or you need to kill some sessions at the OS level. Once you managed to connect, see if you can spot anyone making abnormal numbers of connections. If you can't find anything you will need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect:
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150

SQL> select count(*) from v$process;

COUNT(*)
----------
149

SQL> alter system set processes=300 scope=spfile;

System altered.

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

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 300



Note. The maximum allowed processes is one less than the init parameter 'processes'

No comments: