SESSIONS - Configure Maximum Oracle Database Sessions

SESSIONS

Overview

SESSIONS specifies the maximum number of concurrent sessions (both user sessions and background process sessions) that can exist in an Oracle instance at one time. Unlike PROCESSES, which counts OS-level processes, SESSIONS counts logical Oracle sessions — including recursive sessions created internally by Oracle for triggers, stored procedures, and replication. When the limit is hit, new connection attempts fail with ORA-00018.

SESSIONS is a derived parameter: when not set explicitly, Oracle calculates it as 1.5 × PROCESSES + 22 at startup. This formula provides headroom for recursive sessions. In most environments, the derived value is appropriate. You typically only set SESSIONS explicitly when you need to lower it (to conserve SGA memory structures) or when the derived value diverges from your actual session profile.

Parameter Type: Static (derived from PROCESSES at startup; requires restart to change) Default Value: Derived: 1.5 × PROCESSES + 22 Valid Range: 1 to OS-dependent maximum (practical ceiling is OS memory) Available Since: All Oracle versions Modifiable: No — set in SPFILE; requires instance restart PDB Modifiable: Yes (12c+) — PDB-level session limits can be set with ALTER SYSTEM inside a PDB

Configuration

Viewing Current Value

-- Check SESSIONS value and whether it was explicitly set or derived
SELECT name,
       value,
       isdefault,
       description
FROM   v$parameter
WHERE  name IN ('sessions', 'processes', 'transactions')
ORDER BY name;

-- Check what is in SPFILE (null value means it is derived, not explicitly set)
SELECT name, value, display_value
FROM   v$spparameter
WHERE  name IN ('sessions', 'processes', 'transactions');

-- Real-time limit vs usage — the most important operational check
SELECT resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value,
       ROUND(current_utilization / NULLIF(limit_value, 0) * 100, 1) AS current_pct,
       ROUND(max_utilization     / NULLIF(limit_value, 0) * 100, 1) AS peak_pct
FROM   v$resource_limit
WHERE  resource_name IN ('sessions', 'processes', 'transactions')
ORDER BY resource_name;

Setting the Parameter

-- Scenario 1: Let Oracle derive SESSIONS from PROCESSES (recommended default)
-- Only set PROCESSES; SESSIONS auto-calculates to 1.5 * PROCESSES + 22
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;
-- SESSIONS will be 772 after restart

-- Scenario 2: Set SESSIONS explicitly (when you need a specific value)
ALTER SYSTEM SET sessions = 800 SCOPE = SPFILE;
-- Note: SESSIONS must be >= PROCESSES (Oracle enforces this)

-- Scenario 3: Raise both together for a large-scale deployment
ALTER SYSTEM SET processes = 1000 SCOPE = SPFILE;
ALTER SYSTEM SET sessions  = 1522 SCOPE = SPFILE;  -- 1.5 * 1000 + 22

-- Verify SPFILE will produce the correct values at next startup
SELECT name, value
FROM   v$spparameter
WHERE  name IN ('processes', 'sessions', 'transactions');

-- PDB-level sessions limit (12c+, run inside the PDB)
-- Limits sessions for this PDB without affecting CDB-level parameter
ALTER SYSTEM SET sessions = 200;   -- inside a PDB, takes effect immediately

Tuning Guidance

Relationship Between PROCESSES, SESSIONS, and TRANSACTIONS

These three parameters are coupled. Changing PROCESSES changes the derived values of SESSIONS and TRANSACTIONS unless they are set explicitly.

PROCESSES  = number of OS processes (background + foreground)
SESSIONS   = PROCESSES * 1.5 + 22   (derived default)
TRANSACTIONS = SESSIONS * 1.1       (derived default)

-- Calculate expected derived values for a given PROCESSES setting
SELECT 500                           AS processes_setting,
       ROUND(500 * 1.5 + 22)        AS derived_sessions,
       ROUND((500 * 1.5 + 22) * 1.1) AS derived_transactions
FROM   dual;

-- Verify current derived vs explicit settings
SELECT p.value                             AS processes_limit,
       s.value                             AS sessions_limit,
       ROUND(p.value * 1.5 + 22)          AS expected_derived_sessions,
       CASE WHEN s.isdefault = 'TRUE'
            THEN 'DERIVED'
            ELSE 'EXPLICIT' END            AS sessions_source
FROM   v$parameter p, v$parameter s
WHERE  p.name = 'processes'
AND    s.name = 'sessions';

Recommended Values

Environment	PROCESSES	Derived SESSIONS	Explicit SESSIONS
Development	150	247	Usually not needed
Small production	300	472	Usually not needed
Medium production	500	772	Set explicitly if > 772 needed
Large production	1000	1522	Set explicitly for precision
Connection pooled	300	472	Match pool max connections
RAC (per node)	500–1000	Per-node derived	Size each node independently

The 1.5× multiplier accounts for recursive sessions: Oracle spawns internal sessions for triggers, foreign key checks, and distributed transaction coordination. In highly normalized schemas with many triggers, recursive sessions can account for 20–30% of total session count.

-- Check how many recursive/background sessions are active right now
SELECT CASE
         WHEN username IS NULL     THEN 'Background/Recursive'
         ELSE 'User Session'
       END                            AS session_type,
       status,
       COUNT(*)                       AS session_count
FROM   v$session
GROUP BY
       CASE WHEN username IS NULL THEN 'Background/Recursive' ELSE 'User Session' END,
       status
ORDER BY session_type, status;

How to Size

-- Step 1: Establish current peak usage (most critical metric)
SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value,
       ROUND(max_utilization / NULLIF(limit_value, 0) * 100, 1) AS peak_pct
FROM   v$resource_limit
WHERE  resource_name = 'sessions';

-- Step 2: Profile session types and status
SELECT status,
       type,
       COUNT(*)                    AS session_count
FROM   v$session
GROUP BY status, type
ORDER BY session_count DESC;

-- Step 3: Sessions by application/machine
SELECT machine,
       program,
       username,
       status,
       COUNT(*) AS sessions
FROM   v$session
WHERE  username IS NOT NULL
GROUP BY machine, program, username, status
ORDER BY sessions DESC
FETCH FIRST 20 ROWS ONLY;

-- Step 4: Long-idle sessions (potential connection leak candidates)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.program,
       s.last_call_et               AS idle_seconds,
       ROUND(s.last_call_et / 3600, 1) AS idle_hours,
       s.logon_time
FROM   v$session s
WHERE  s.username IS NOT NULL
AND    s.status   = 'INACTIVE'
AND    s.last_call_et > 3600       -- idle more than 1 hour
ORDER BY s.last_call_et DESC;

-- Step 5: Size recommendation
-- Target: SESSIONS = max_utilization * 1.25, rounded up, then set PROCESSES accordingly
-- If max_utilization = 600, target SESSIONS = 750
-- Derive PROCESSES: (750 - 22) / 1.5 = 485, set PROCESSES = 500

Monitoring

-- Continuous monitoring: sessions and processes together
SELECT r.resource_name,
       r.current_utilization     AS current,
       r.max_utilization         AS peak_ever,
       r.limit_value             AS max_allowed,
       ROUND(r.current_utilization / NULLIF(r.limit_value, 0) * 100, 1) AS current_pct,
       ROUND(r.max_utilization     / NULLIF(r.limit_value, 0) * 100, 1) AS peak_pct
FROM   v$resource_limit r
WHERE  r.resource_name IN ('sessions', 'processes', 'transactions');

-- Session growth over the last hour (useful for capacity trending)
SELECT TO_CHAR(logon_time, 'HH24:MI')  AS logon_hour_minute,
       COUNT(*)                         AS sessions_logged_on
FROM   v$session
WHERE  username  IS NOT NULL
AND    logon_time >= SYSDATE - 1/24
GROUP BY TO_CHAR(logon_time, 'HH24:MI')
ORDER BY TO_CHAR(logon_time, 'HH24:MI');

-- AWR: historical session count (requires Diagnostics Pack)
SELECT s.snap_id,
       TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
       s.num_sess
FROM   dba_hist_snapshot sn
JOIN  (
    SELECT snap_id,
           COUNT(*) AS num_sess
    FROM   dba_hist_active_sess_history
    GROUP BY snap_id
) s ON s.snap_id = sn.snap_id
WHERE  sn.begin_interval_time >= SYSDATE - 7
ORDER BY s.snap_id;

Common Issues

ORA-00018: Maximum number of sessions exceeded

Occurs when the SESSIONS limit is reached and a new session attempts to connect. Unlike PROCESSES, a SYSDBA connection counts against SESSIONS (background processes reserve slots, but SYSDBA foreground sessions do not bypass the limit). However, Oracle typically reserves 5 sessions for SYSDBA connections.

Immediate triage:

-- Connect as SYSDBA (Oracle reserves a small pool of sessions for this)
-- sqlplus / as sysdba

-- Confirm limit is hit
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name = 'sessions';

-- Find sessions eligible for immediate termination
-- Priority: inactive, long-idle, or disconnected (SNIPED status)
SELECT sid,
       serial#,
       username,
       status,
       last_call_et    AS idle_seconds,
       machine,
       program,
       logon_time
FROM   v$session
WHERE  status IN ('INACTIVE', 'SNIPED')
AND    username IS NOT NULL
ORDER BY last_call_et DESC
FETCH FIRST 30 ROWS ONLY;

-- Kill selected sessions to free slots
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Permanent fix (restart required):

ALTER SYSTEM SET processes = 600 SCOPE = SPFILE;
-- SESSIONS auto-derives to 922; or set it explicitly
ALTER SYSTEM SET sessions  = 900 SCOPE = SPFILE;
-- Plan maintenance window restart

See the full ORA-00018 guide.

PDB session limits in a multitenant environment

In Oracle 12c+, individual PDBs can have their own session limits set independently. A PDB exhausting its session limit produces ORA-00018 even if the CDB still has session capacity.

-- Check PDB-level session limits (run in CDB$ROOT)
SELECT con_id,
       name,
       value
FROM   v$system_parameter
WHERE  name = 'sessions'
AND    con_id > 0
ORDER BY con_id;

-- Check per-PDB session usage
SELECT con_id,
       SUM(CASE WHEN username IS NOT NULL THEN 1 ELSE 0 END) AS user_sessions,
       COUNT(*) AS total_sessions
FROM   gv$session
GROUP BY con_id
ORDER BY con_id;

-- Increase session limit for a specific PDB
ALTER SESSION SET CONTAINER = pdb_name;
ALTER SYSTEM SET sessions = 300;
ALTER SESSION SET CONTAINER = cdb$root;

TRANSACTIONS limit hit before SESSIONS limit

TRANSACTIONS defaults to 1.1 × SESSIONS. In workloads with many concurrent active transactions (e.g., batch jobs), TRANSACTIONS can be exhausted before SESSIONS. ORA-01575 or rollback segment errors may appear.

-- Check TRANSACTIONS usage
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name = 'transactions';

-- Increase TRANSACTIONS (or raise SESSIONS which increases derived TRANSACTIONS)
ALTER SYSTEM SET transactions = 1100 SCOPE = SPFILE;

Parameter	Relationship
`PROCESSES`	Primary driver: `SESSIONS = 1.5 * PROCESSES + 22` (derived default)
`TRANSACTIONS`	Derived from SESSIONS: `1.1 * SESSIONS`; governs undo segment headers
`LICENSE_MAX_SESSIONS`	Soft limit for concurrent sessions for license compliance
`IDLE_TIME` (profile)	User profile setting to disconnect idle sessions; reduces session buildup
`CONNECT_TIME` (profile)	User profile setting to limit maximum session duration
`RESOURCE_LIMIT`	Must be `TRUE` for user profile resource limits (IDLE_TIME, etc.) to be enforced

Version Notes

Version	Notes
All versions	Derived from PROCESSES; formula `1.5 * PROCESSES + 22` is consistent
9i+	Setting SESSIONS explicitly overrides the derived value
12c	PDBs can have their own SESSIONS limit set via `ALTER SYSTEM` inside the PDB
19c	CDB with many PDBs increases background session count significantly; re-check sizing
21c+	No formula change; PDB-level limits are the recommended isolation mechanism in multitenant