ORA-00020: Maximum Number Of Processes Exceeded Solution

In the Oracle database, we face an “ORA-00020: maximum number of processes exceeded” error while making a connection with the oracle database via sqlplus and the Application may encounter TNS errors. This practice we performing on Linux platform.

Read how to solve ORA-12954

Table of Contents

ORA-00020: maximum number of processes exceeded

If you face ORA-00020 maximum number of processes which means the process parameter is exceeded. Now we need to increase processes parameters in the Oracle database using the below commands.

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

System altered.

But there is an issue with this parameter it will be activated after the database bounce. So, if you don’t want to restart your database, then you can solve this problem to kill inactive sessions using the below command.

SQL> select 'kill -9 ' || p.SPID, s.USERNAME, 'alter system kill session '''||sid||',' || s.serial# || ''';',s.STATUS
from v$session s, v$process p
where s.PADDR = p.ADDR (+)
and s.STATUS='INACTIVE' 
order by 1;

Execute the below commands to kill the sessions

alter system kill session '117,17120';
alter system kill session '116,20433';
alter system kill session '117,12140';
alter system kill session '118,14151';

OR kill session at OS level

kill -9 32152
kill -9 32562
kill -9 32891
kill -9 32981

ORA-00020 SOLUTION

Change the process parameter value and take bounce the database. ora-00020 solution.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 300

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

System altered.

Restart the Database

I recommend before taking the restart you must kill all inactive sessions.

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

SQL> startup
ORACLE instance started.
Total System Global Area 234463264 bytes
Fixed Size 2430072 bytes
Variable Size 3240872 bytes
Database Buffers 134549376 bytes
Redo Buffers 4242144 bytes
Database mounted.
Database opened.

Now finally we solved the ORA-00020: maximum number of processes exceeded.