ORA-00020: Maximum Number of Processes Exceeded — What It Means and How to Fix It

Oracle error ORA-00020: maximum number of processes (string) exceeded indicates that your database has hit its process limit, as defined by the PROCESSES initialization parameter. Once this limit is reached, no new sessions or background processes can connect until others are closed or the limit is increased.

This guide provides clear, production-tested steps to resolve and prevent this issue.

1. Emergency Access (When You Can’t Log In Normally)

If you’re locked out (even as SYSDBA), log in from the database server using OS authentication:

sqlplus / as sysdba

Note: This requires OS-level access as the Oracle user on the server (e.g., oracle).

2. Check Process Usage and Limits

Start by checking how many processes are currently in use:

SET LINES 150
SET PAGES 50
COL resource_name FORMAT A20
COL current_utilization FORMAT 999
COL max_utilization FORMAT 999
COL limit_value FORMAT A10

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name = 'processes';
or

select * from v$resource_limit;

Note: If current_utilization is consistently near limit_value, consider increasing the limit or investigating excessive session usage.

3. View Session Count by User and Status

To identify which users are consuming the most sessions (especially inactive ones):

SET LINESIZE 150 
SET PAGESIZE 50
COL STATUS FORMAT A15
COL COUNT FORMAT 99999
COL USERNAME FORMAT A25

SELECT NVL(s.username, 'UNKNOWN') AS username, s.status, COUNT(1) AS session_count 
FROM v$process p, v$session s
WHERE paddr(+) = addr
GROUP BY NVL(s.username, 'UNKNOWN'), s.status
ORDER BY s.status;

Use this to spot high-volume users or tools with potential connection leaks.

4. Investigate Inactive Sessions for a Specific User

Check which sessions for a user (e.g., W3BUDDY) are inactive:

SET LINES 300
SET PAGES 200
COL machine FOR A30
COL status FOR A20

SELECT sid, serial#, username, program, machine, status, sql_id, logon_time
FROM v$session
WHERE status = 'INACTIVE'
AND username = 'W3BUDDY'
AND type <> 'BACKGROUND';

5. Generate Kill Commands for Truly Idle Sessions

If sessions are inactive and have no active SQL (sql_id IS NULL), you can safely kill them:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE status = 'INACTIVE'
AND username = 'W3BUDDY'
AND type <> 'BACKGROUND'
AND sql_id IS NULL;

Note: Be cautious when killing sessions. Terminating the wrong session can disrupt application users or background jobs.

For additional session management commands, see: Oracle Session Management

6. Permanently Increase the PROCESSES Limit

To avoid hitting this error again, increase the limit based on expected load.

Check the current setting:

SHOW PARAMETER processes;

Update it (if using an spfile):

ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

If you’re using a pfile, edit the init.ora file:

PROCESSES=500

Then restart the database.

Note: A restart is required for this change to take effect.

7. Identify Long-Running or Stale Sessions

You can spot sessions that have been open for hours and are likely idle:

SELECT sid, serial#, username, logon_time, status, program
FROM v$session
WHERE username IS NOT NULL
AND logon_time < SYSDATE - (2/24)
ORDER BY logon_time;

This is useful for proactive cleanup before you hit the limit again.

Best Practices to Prevent ORA-00020

Use proper connection pooling in applications (e.g., HikariCP, UCP)
Configure idle timeouts for application sessions
Monitor session usage with automated scripts or alerts
Periodically audit for inactive or zombie sessions
Scale PROCESSES based on peak load + background services

Conclusion

The ORA-00020 error is a capacity issue, not a failure. The solution is twofold: clean up unneeded sessions and adjust your database to scale with usage.

For long-term stability, implement monitoring and enforce connection best practices across applications and tools.