{"id":4603,"date":"2025-07-01T02:58:01","date_gmt":"2025-07-01T02:58:01","guid":{"rendered":"https:\/\/w3buddy.com\/?p=4603"},"modified":"2026-01-15T12:44:07","modified_gmt":"2026-01-15T07:14:07","slug":"ora-00020-maximum-number-of-processes-exceeded-what-it-means-and-how-to-fix-it","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/ora-00020-maximum-number-of-processes-exceeded-what-it-means-and-how-to-fix-it\/","title":{"rendered":"ORA-00020: Maximum Number of Processes Exceeded \u2014 What It Means and How to Fix It"},"content":{"rendered":"\n

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

This guide provides clear, production-tested steps to resolve and prevent this issue.<\/p>\n\n\n\n

1. Emergency Access (When You Can\u2019t Log In Normally)<\/h2>\n\n\n\n

If you’re locked out (even as SYSDBA), log in from the database server using OS authentication:<\/p>\n\n\n\n

sqlplus \/ as sysdba\n<\/code><\/code><\/pre>\n\n\n\n
Note:<\/strong> This requires OS-level access as the Oracle user on the server (e.g., oracle<\/code>).<\/p>\n\n\n\n
2. Check Process Usage and Limits<\/h2>\n\n\n\n
Start by checking how many processes are currently in use:<\/p>\n\n\n\n
SET LINES 150\nSET PAGES 50\nCOL resource_name FORMAT A20\nCOL current_utilization FORMAT 999\nCOL max_utilization FORMAT 999\nCOL limit_value FORMAT A10\n\nSELECT resource_name, current_utilization, max_utilization, limit_value\nFROM v$resource_limit\nWHERE resource_name = 'processes';<\/code>\nor<\/em>\n\nselect * from v$resource_limit;<\/code><\/pre>\n\n\n\n
Note:<\/strong> If current_utilization<\/code> is consistently near limit_value<\/code>, consider increasing the limit or investigating excessive session usage.<\/p>\n\n\n\n
3. View Session Count by User and Status<\/h2>\n\n\n\n
To identify which users are consuming the most sessions (especially inactive ones):<\/p>\n\n\n\n
SET LINESIZE 150 \nSET PAGESIZE 50\nCOL STATUS FORMAT A15\nCOL COUNT FORMAT 99999\nCOL USERNAME FORMAT A25\n\nSELECT NVL(s.username, 'UNKNOWN') AS username, s.status, COUNT(1) AS session_count \nFROM v$process p, v$session s\nWHERE paddr(+) = addr\nGROUP BY NVL(s.username, 'UNKNOWN'), s.status\nORDER BY s.status;<\/code><\/code><\/pre>\n\n\n\n
Use this to spot high-volume users or tools with potential connection leaks.<\/p>\n\n\n\n
4. Investigate Inactive Sessions for a Specific User<\/h2>\n\n\n\n
Check which sessions for a user (e.g., W3BUDDY<\/code>) are inactive:<\/p>\n\n\n\n
SET LINES 300\nSET PAGES 200\nCOL machine FOR A30\nCOL status FOR A20\n\nSELECT sid, serial#, username, program, machine, status, sql_id, logon_time\nFROM v$session\nWHERE status = 'INACTIVE'\nAND username = 'W3BUDDY'\nAND type <> 'BACKGROUND';<\/code><\/code><\/pre>\n\n\n\n
5. Generate Kill Commands for Truly Idle Sessions<\/h2>\n\n\n\n
If sessions are inactive and<\/strong> have no active SQL (sql_id IS NULL<\/code>), you can safely kill them:<\/p>\n\n\n\n
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd\nFROM v$session\nWHERE status = 'INACTIVE'\nAND username = 'W3BUDDY'\nAND type <> 'BACKGROUND'\nAND sql_id IS NULL;<\/code><\/code><\/pre>\n\n\n\n
Note:<\/strong> Be cautious when killing sessions. Terminating the wrong session can disrupt application users or background jobs.<\/p>\n\n\n\n
For additional session management commands, see: Oracle Session Management<\/a><\/p>\n\n\n\n
6. Permanently Increase the PROCESSES Limit<\/h2>\n\n\n\n
To avoid hitting this error again, increase the limit based on expected load.<\/p>\n\n\n\n
Check the current setting:<\/p>\n\n\n\n
SHOW PARAMETER processes;<\/code><\/code><\/pre>\n\n\n\n
Update it (if using an spfile<\/code>):<\/p>\n\n\n\n
ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE;\nSHUTDOWN IMMEDIATE;\nSTARTUP;<\/code><\/code><\/pre>\n\n\n\n
If you’re using a pfile<\/code>, edit the init.ora<\/code> file:<\/p>\n\n\n\n
PROCESSES=500<\/code><\/code><\/pre>\n\n\n\n
Then restart the database.<\/p>\n\n\n\n
Note:<\/strong> A restart is required for this change to take effect.<\/p>\n\n\n\n
7. Identify Long-Running or Stale Sessions<\/h2>\n\n\n\n
You can spot sessions that have been open for hours and are likely idle:<\/p>\n\n\n\n
SELECT sid, serial#, username, logon_time, status, program\nFROM v$session\nWHERE username IS NOT NULL\nAND logon_time < SYSDATE - (2\/24)\nORDER BY logon_time;<\/code><\/code><\/pre>\n\n\n\n
This is useful for proactive cleanup before you hit the limit again.<\/p>\n\n\n\n
Best Practices to Prevent ORA-00020<\/h2>\n\n\n\n
    \n
  • Use proper connection pooling<\/strong> in applications (e.g., HikariCP, UCP)<\/li>\n\n\n\n
  • Configure idle timeouts<\/strong> for application sessions<\/li>\n\n\n\n
  • Monitor session usage with automated scripts or alerts<\/li>\n\n\n\n
  • Periodically audit for inactive or zombie sessions<\/li>\n\n\n\n
  • Scale PROCESSES<\/code> based on peak load + background services<\/li>\n<\/ul>\n\n\n\n
    Conclusion<\/h2>\n\n\n\n
    The ORA-00020<\/code> error is a capacity issue, not a failure. The solution is twofold: clean up unneeded sessions and adjust your database to scale with usage.<\/p>\n\n\n\n
    For long-term stability, implement monitoring and enforce connection best practices across applications and tools.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-4603","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4603","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4603"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4603\/revisions"}],"predecessor-version":[{"id":4604,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4603\/revisions\/4604"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}