Print Friendly, PDF & Email
What is the query to get the process limit in the Oracle database?
SYMPTOMS

The following incident is being generated

Host=dbpilot.net
Target type=Database Instance
Target name=ORCL_dbpilot.net
Categories=Capacity
Message=Metrics Process Limit % is at 98
Severity=Warning
Event reported time=Aug 11, 2020 4:46:01 PM MSK
Comment=
Operating System=Linux
Platform=x86_64
Event Type=Metric Alert
Event name=Database_Resource_Usage:process_usage
Metric Group=Database Limits
Metric=Process Limit Usage (%)
Metric value=98
Key Value=
Rule Name=ROOT_NOTIFICATION_RULE,ALL TARGET EVENTS
Rule Owner=SYSMAN
Update Details:
Metrics Process Limit % is at 98

Error message in the alert log of a problematic database confirms the same

Tue Aug 11 17:05:11 2020
ORA-00020: maximum number of processes (800) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process W001 submission failed with error = 20

All login attempts to the problematic database fail with the error

[oracle@dbpilot.net ~]$ sqlplus dbsnmp/password

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 11 17:44:12 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-00020: maximum number of processes (800) exceeded

Enter user-name:

CAUSE

The maximum number of user PROCESSES has reached the allowed limit. The database can't create additional processes beyond that limit.

To determine the current value for the maximum allowed processes and the current processes utilization, execute the following SQL query

SET LINES 300
SET PAGES 999
COL RESOURCE_NAME FOR A15
COL CURRENT FOR 999999
COL MAX FOR 99999
COL LIMIT FOR 99999

SELECT RESOURCE_NAME,
       CURRENT_UTILIZATION "CURRENT",
       MAX_UTILIZATION "MAX_REACHED", 
       LIMIT_VALUE "LIMIT",
       ROUND(CURRENT_UTILIZATION/LIMIT_VALUE*100,1) PCT_USED
FROM 
       V$RESOURCE_LIMIT
WHERE
       RESOURCE_NAME IN ( 'sessions', 'processes');
RESOURCE_NAME   CURRENT MAX_REACHED     LIMIT    PCT_USED
--------------- ------- ----------- ---------- ----------
processes           799         800        800       99.9
sessions            815         822       1224       66.6

Written At
11 AUG 202023:00
Red Hat Release
7.x x64
Enterprise Manager
13.3.0.0.0
Database Release
11.2.0.4 - 19.13.0.0.0