Troubleshoot Fix Resolve Ora-00020: Maximum Number Of Processes (num) Exceeded

advertisements

_____________________________________________________________________________________________________________________

Overview

This is a database resource error. The error is because of the number of oracle processes reached its maximum limit. The maximum limit is mentioned in your database parameter file (spfile/pfile). Corresponding init parameter is PROCESSES. 

How you can check the value of this parameter

1. Open init.ora file and check the value of the parameter proceses
2. Connect sqlplus sys as sysdaba

SQL> show parameter processes

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------------processes                            integer     400

3. Connect sqlplus sys as sysdaba

SQL> select name , value from v$parameter where name ='processes';

NAME                 VALUE

-------------------- ------------------------------

processes            400

Permanent fix for this error

The permanent fix for this issue is increase the value of the process parameter.

1. If your database is opened with spfile then,Alter system set sessions=600 scope=spfile;
   shutdown immediate;
   startup;
2. If Your database is opened with pfile then,
   Open your parameter file(init.ora) with an editor and modify the processes parameter value to higher value. Restart the database after making the changes to the parameter file.

Quick fix/workaround

If you are not at all able to login to the database, or you cannot bounce the database without maintenance window you can go ahead with following workaround.

For Unix: Find out some old idle oracle session process and kill them using OS kill command

$  ps -ef|grep LOCAL=NO|grep oracleproddb

  oracle  1994     1   0 16:34:27 ?           0:01 oracleproddb (LOCAL=NO)

  oracle  2202     1   0 16:38:05 ?           0:00 oracleproddb (LOCAL=NO)

  oracle  2400     1   0 16:39:06 ?           2:14 oracleproddb (LOCAL=NO)

$ kill -9 1994

For Windows:

kill some old sessions using ORAKILL utility.

Using SQLPLUS with sys as sysdba privilege:

If someone already logged in with sqlplus with sysdba privilege, find out some old idle connections from v$session dictionary view using login time and kill those sessions 

alter system kill session ‘sid, serial#’;

 

Recommendation

                If your database was quite stable for long time and one fine day it is giving problem, in that case it is recommended to check the application also whether it is spawning unwanted connections or process.

_____________________________________________________________________________________________________________________

Website Stats