How to check session and process resource limits in Oracle

V$RESOURCE_LIMIT tracks the current utilization, max reached since startup, and configured LIMIT for named resources — the two you usually care about are ''sessions'' and ''processes''. Three views: single-instance current, RAC-aware with %, and historical from AWR.

The third query uses DBA_HIST_RESOURCE_LIMIT and DBA_HIST_SNAPSHOT — part of Oracle Diagnostic Pack and requires that license on Enterprise Edition.

-- 1) Current values (single-instance)
SELECT resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value AS allocated
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

-- 2) RAC-aware, with usage percentage
SELECT inst_id,
       resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value,
       ROUND(current_utilization * 100 / initial_allocation, 2) AS "Process limit %"
FROM   gv$resource_limit
WHERE  resource_name IN ('sessions', 'processes');

-- 3) Historical, from AWR (sessions only — filter as needed)
COL snapshot_time FORMAT a20
COL resource_name FORMAT a20

SELECT s.snap_id,
       TO_CHAR(s.end_interval_time, 'DD-MON HH24:MI:SS') AS snapshot_time,
       b.resource_name,
       b.current_utilization,
       b.max_utilization,
       b.limit_value
FROM   dba_hist_snapshot       s,
       dba_hist_resource_limit b
WHERE  s.snap_id       = b.snap_id
AND    b.resource_name = 'sessions'
ORDER BY snapshot_time;