DBA Resource Limits – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Database Administration · all Database Administration reports
Overview
Current and highest utilization since instance startup of database resources such as processes, sessions and transactions, compared to their limits.
A Max Percent close to 100 shows that the instance reached the limit since startup. Reaching the processes or sessions limit causes ORA-00020 or ORA-00018 for new connections.
In a pluggable database, the database shows resource limits only in the container root. There the report shows the current number of processes and sessions against their parameter limits instead, without the highest utilization.
Schedule the report with Max Percent From, for example 90, to get an email only when a limit is nearly reached.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Max Percent From, Instance Id
Oracle EBS Tables Used
gv$resource_limit, gv$process, gv$session, gv$parameter
Report Categories
Related Reports
DBA Sessions, FND Concurrent Requests, Blitz Report History, DBA SGA SQL Performance Summary, DBA SGA+PGA Memory Configuration, DBA AWR SQL Performance Summary, DBA Blocking Sessions, Blitz Report Web Service Sessions, DBA Undo Statistics
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | DBA Resource Limits 12-Oct-2026 061635.xlsx |
| Blitz Report™ XML Import | DBA_Resource_Limits.xml |
| SQL Source | DBA Resource Limits.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-resource-limits/ |
Case Study & Technical Analysis: DBA Resource Limits
Executive Summary
DBA Resource Limits shows the current and highest utilization, since instance startup, of the database resources that cap connections and transactions, such as processes, sessions and transactions, against their configured limits. It helps DBAs of Oracle E-Business Suite databases detect when the instance is approaching or has reached a limit, the point at which new connections fail. The report is intended for DBA operations teams who monitor capacity and for administrators who want a scheduled warning before users are affected.
Business Challenge
- Connection Failures: When the processes or sessions limit is reached, new logons fail with ORA-00020 or ORA-00018, which stops users from working.
- Hidden Peak Usage: Current utilization shows the load at one moment, but the highest value since startup shows whether the limit was reached earlier.
- Capacity Planning: Without a comparison against the configured limits, the team cannot judge how much headroom remains.
- Pluggable Database Differences: In a container database the resource views differ between the root and the pluggable databases, so the same check needs different handling.
The Solution
- Limit Comparison: Each resource is shown with its current utilization, its maximum utilization and its configured limit, and the two percentages are calculated against the limit.
- Sorting by Pressure: Rows are ordered by the highest percentage, so the resources closest to their limits appear first.
- Fallback For Containers: Where the resource limit view is empty, as in the container root, the report compares current processes and sessions with their parameter values.
- Alert Scheduling: The Max Percent From parameter lets the report be scheduled so that output is produced only when a limit is nearly reached.
Technical Architecture (High Level)
Primary Tables
GV$RESOURCE_LIMIT: the cluster-wide view of resource usage, with current and maximum utilization and the limit values.GV$PROCESS: used to count current processes for each instance in the fallback branch.GV$SESSION: used to count current sessions for each instance in the fallback branch.GV$PARAMETER: supplies the processes and sessions parameter values for the fallback branch.
Logical Relationships
- The first branch reads every row of
GV$RESOURCE_LIMITfor each instance, trimming the initial allocation and limit text. - The second branch, combined with UNION ALL, reads the processes and sessions parameters from
GV$PARAMETERand only runs whenGV$RESOURCE_LIMITreturns no rows for the instance. - For processes, the current count is taken from
GV$PROCESS; for sessions, it is taken fromGV$SESSION. - An outer query calculates the current and maximum percentages against the numeric limit, leaving them empty where the limit is UNLIMITED or zero.
- Rows are ordered by the larger of the two percentages, then by instance and resource name.
Parameters & Filtering
- Max Percent From: Returns only resources whose maximum percentage is at or above the entered value, for example 90. This is the parameter used for scheduled alerts.
- Instance Id: Restricts the output to one instance of a RAC cluster.
Performance & Optimization
- Small Resource Set: The resource list is short, so the aggregation over sessions and processes is the main cost and is bounded by the number of connections.
- Correlated Count Per Instance: Process and session counts are computed per instance with correlated subqueries, which keeps the counts aligned with each instance.
- Conditional Fallback: The second branch is guarded by a NOT EXISTS test, so it adds work only when the resource limit view is unavailable.
- Ordering on the Computed Percentage: Sorting by the larger percentage places the most constrained resources first without any additional query.
FAQ
Q: What does a Max Percent close to 100 mean? A: The instance reached that percentage of the configured limit at some point since startup. For processes or sessions, reaching 100 means new connections were refused at that point.
Q: Why does my pluggable database show only processes and sessions? A: In a pluggable database the resource limit view shows data only in the container root. The report then compares current processes and sessions with their parameter values, without the highest utilization.
Q: How can the report warn me before users are affected? A: Schedule the report with Max Percent From set to 90. The scheduled run then sends output only when a resource has reached that share of its limit.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH