Resolving ORA-00020: Maximum Number of Processes Exceeded in Oracle

---

The ORA-00020 error occurs when Oracle exceeds the maximum number of processes allowed by the PROCESSES parameter. This setting controls the maximum number of user processes that can connect to the database at the same time. When this limit is reached, new connections will fail, and the error will appear both in the application and in the database alert log.

Identifying the Error

You can detect the error in two primary ways:

1. In the alert log: Use the following command to check the most recent entries in your alert log:

tail -200f alert_<sid>.log

This command will show the last 200 lines and continue to monitor new entries. Look for the ORA-00020 error message.

2. In the application: Applications that fail to connect will display the error message similar to:

ORA-00020: maximum number of processes (%d) exceeded

Resolving the Issue

You have two main ways to address this issue: eliminating unnecessary sessions or increasing the PROCESSES parameter.

1. Identifying and Terminating Excess Sessions

First, identify which sessions are consuming the processes and, if needed, terminate those that are no longer required.

Steps:

• Check the current number of active sessions: SELECT COUNT(*) FROM v$session;
• Identify the sessions that are using processes: SELECT s.sid, s.serial#, s.username, s.program FROM v$session s JOIN v$process p ON s.paddr = p.addr;
• To safely terminate unnecessary sessions:
  sql ALTER SYSTEM KILL SESSION 'sid,serial#';
  Note: Be cautious when terminating sessions to avoid disrupting critical processes.

2. Increasing the PROCESSES Parameter

If the number of sessions is legitimate, you might need to increase the PROCESSES limit to accommodate more connections. First, check the current value of the parameter:

SHOW PARAMETER processes;

To adjust the value:

• Increase the PROCESSES value using the following command:
  sql ALTER SYSTEM SET processes = <new_value> SCOPE=SPFILE;
  Important: Changing the PROCESSES parameter requires a database restart, so plan this during a maintenance window to avoid service interruptions.

Key Considerations

• Monitoring: Regularly monitor the number of processes to ensure your database can handle the load.
• Restart Planning: Coordinate with your operations team and users to choose a suitable time for restarting the database if you decide to change the PROCESSES parameter.

By either terminating excessive sessions or increasing the PROCESSES parameter, you can effectively resolve the ORA-00020 error and ensure your Oracle database runs smoothly.

---