ORA-00020 data resource_error ai_generated true

Oracle ORA-00020: maximum number of processes (150) exceeded

ID: data/oracle-too-many-files-open

Also available as: JSON · Markdown · 中文
92%Fix Rate
90%Confidence
1Evidence
2023-04-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Oracle Database 19c active — — —
Oracle Database 21c active — — —
Oracle Database 12c active — — —

Root Cause

Oracle database's processes parameter limits the total number of OS processes (including connections and background processes). When connection pools or application servers open more connections than the limit, new connections are rejected.

generic

中文

Oracle数据库的processes参数限制了操作系统进程总数（包括连接和后台进程）。当连接池或应用服务器打开的连接数超过限制时，新连接将被拒绝。

Official Documentation

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ORA-00020.html

Workarounds

  1. 95% success Increase the processes parameter in the SPFILE and restart the database to allow more connections. 
    Increase the processes parameter in the SPFILE and restart the database to allow more connections.
  2. 80% success Reduce connection pool size in the application to stay within the current limit, e.g., set max connections to 100 in HikariCP. 
    Reduce connection pool size in the application to stay within the current limit, e.g., set max connections to 100 in HikariCP.

中文步骤

  1. Increase the processes parameter in the SPFILE and restart the database to allow more connections.
  2. Reduce connection pool size in the application to stay within the current limit, e.g., set max connections to 100 in HikariCP.

Dead Ends

Common approaches that don't work:

  1. 99% fail

    The processes parameter remains unchanged, so the same limit applies after restart.

  2. 90% fail

    Sessions depend on processes; without increasing processes, the session limit is unreachable.