Tuesday, March 11, 2014

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

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limi
where resource_name in ('sessions', 'processes');

When ORA-00020: maximum number of processes (%s) exceeded occurred in a database and also you are NOT able to connect to the database as sys even to increase the processes parameter.
In that scenario following will work to resolve the same.

1. Instead of logging to the database via sys user as sqlplus / as sysdba you can use the following command.
sqlplus -prelim "/ as sysdba"

2.  Then you can shutdown the database using shutdown abort
SQL> shutdown abort

3. Then connect to the database via sys user as follows.
sqlplus / as sysdba

4. Then mount the database and increase the processes parameter as required.
SQL> startup mount

5. Then open the database.
SQL> alter database open