The ORA-00020 error means that the maximum number of processes allowed by the Oracle database has been reached.
ORA-00020-Maximum number of processes exceeded
Cause:
This can happen if there are too many users trying to connect to the database at the same time, or if a single user is running a long-running query or transaction.
Resolution:
ORA-00020 comes under “Oracle Database Server Messages”. These messages are generated by the Oracle database server when running any Oracle program. Increase the value of the PROCESSES initialization parameter.
Step 1:
Check the v$process view to see a list of all the current processes in the database. This can help you identify which processes are using the most resources and may be causing the error.
Step 2:
Check the v$resource_limit view to see the current maximum number of sessions allowed by the database. This can help you verify that the SESSIONS parameter has been set correctly.
To get more visibility, let’s double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:
Step 3:
If you are using a load balancer, check the load balancer’s logs to see if it is rejecting any connections to the database.
Step 4:
If you are using a firewall, check the firewall’s rules to make sure that it is not blocking any connections to the database.
Step 5:
Method-1
- To fix the ORA-00020 error, you can increase the maximum number of processes allowed by the database. This can be done by changing the
processesinitialization parameter in the database’s configuration file. Theprocessesparameter specifies the maximum number of concurrent processes that can be running in the database at the same time. - To increase the
processesparameter, you will need to edit the database’s configuration file. The configuration file is typically namedinit.oraand is located in theORACLE_HOME/dbsdirectory. - To increase the Processes parameter, you will need to change the value of the parameter. The new value should be larger than the current number of sessions that are connected to the database. For example, if the current number of sessions is 100, you would need to change the value of the SESSIONS parameter to 150.
Method- 2
You can check for idle or inactive sessions and terminate them to free up resources. Run the following SQL query as a privileged user to identify idle sessions:
SELECT spid, username, program
FROM v$process p JOIN v$session s ON p.addr = s.paddr
WHERE s.status = ‘ACTIVE’ AND s.type = ‘USER’;
Once you have identified the sessions, you can terminate them using the ALTER SYSTEM KILL SESSION command:
ALTER SYSTEM KILL SESSION ‘<side>,<serial#>’ ;
Method-3
You can increase these 3 parameters according to following formula.
processes=x
sessions=x1.2 transactions=sessions1.2
If you are planning to increase “sessions” parameter you should also plan to increase “processes” and “transactions” parameters.
SQL> alter system set processes=1200 scope=spfile;
SQL> alter system set sessions=1200 scope=spfile;
SQL> alter system set transactions=1440 scope=spfile;
Step 6:
Once you have changed the value of the Processes parameter, you will need to save the configuration file and restart the database.
After the database has restarted, you should be able to connect to the database without receiving the ORA-00018 error.
SQL> shutdown immediate
SQL> startup
It’s important to note that modifying database parameters and terminating sessions should be performed carefully and with proper understanding in order to resolve error ORA-00020-Maximum number of Processes exceeded. If you’re unsure or need further assistance, it’s recommended to consult with a database administrator or Oracle support.
