Blog

As a DBA, we often encounter the ORA-00020 error. Today we will discuss how to deal with that situation with detailed steps.

Steps to resolve ORA-00020 Error :

Step 1:

If you can login to the database, then check the current process utilization and kill some inactive sessions by issuing the below command :

select CURRENT_UTILIZATION,MAX_UTILIZATION,RESOURCE_NAME from v$resource_limit;
select sid,serial#,machine,username from v$session where status='INACTIVE';

alter system kill session 'sid,serial#';

Step 2:

If you are not able to login to the database or if the database seems hung, that time you can kill some of the LOCAL=NO processes by issuing the below command :

Command :
ps -ef|grep -i local=no | grep TEST

Output :
oracle   64373     1  0 Jun08 ?        00:00:03 oracleTEST (LOCAL=NO)
oracle   64540     1  0 Jun08 ?        00:00:00 oracleTEST (LOCAL=NO)
oracle   64554     1  0 Jun08 ?        00:00:01 oracleTEST (LOCAL=NO)
oracle   64633     1  0 Jun08 ?        00:00:03 oracleTEST (LOCAL=NO)
oracle   64637     1  0 Jun08 ?        00:00:00 oracleTEST (LOCAL=NO)
.
.
.
oracle   65186     1  0 Jun08 ?        00:00:04 oracleTEST (LOCAL=NO)
oracle   65190     1  0 Jun08 ?        00:00:00 oracleTEST (LOCAL=NO)
oracle   65192     1  0 Jun08 ?        00:00:01 oracleTEST (LOCAL=NO)
oracle   65202     1  0 Jun08 ?        00:00:00 oracleTEST (LOCAL=NO)
oracle   65206     1  0 Jun08 ?        00:00:02 oracleTEST (LOCAL=NO)

Kill from OS prompt as below :

kill -9 64373 64540 64554 64633 64637

Step 3:

Hope one of the above steps will resolve to come down the situation. If that doesn’t resolve ORA-00020, you can increase the process count by issuing the below command, but to reflect you need to restart the database.

SQL> show parameter process;

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

SQL> shut immediate;

SQL> startup;

SQL> show parameter process;

Also, you may ask the Application Team to check if they can bounce their application or if you can find more details what caused the process increases. Hope the article helps to resolve the ORA-00020 error.

Also, you can find Oracle Doc for further references of this ORA-00020 error.