PROCESSES - Set Maximum Oracle Database Processes

PROCESSES

Overview

PROCESSES specifies the maximum number of operating system processes that can simultaneously connect to an Oracle instance. This includes background processes (SMON, PMON, DBWn, LGWR, ARCn, and others), foreground processes (user connections in dedicated server mode), and server processes for shared server. Because every connected session in dedicated mode maps to exactly one OS process, PROCESSES is the foundational resource limit for connection capacity.

Oracle automatically derives SESSIONS and TRANSACTIONS from PROCESSES at startup (unless they are set explicitly). Setting PROCESSES too low causes ORA-00020 errors when the limit is hit. Setting it too high wastes virtual memory because Oracle pre-allocates process state area structures. The correct value is sized from observed peak usage plus an adequate safety margin.

Parameter Type: Static (requires instance restart) Default Value: 100 Valid Range: 6 (minimum to support background processes) to OS-dependent maximum Available Since: All Oracle versions Modifiable: No — must be set in SPFILE; requires instance restart PDB Modifiable: No — instance-level resource limit

Configuration

Viewing Current Value

-- Check PROCESSES, SESSIONS, and TRANSACTIONS together
SELECT name,
       value,
       description
FROM   v$parameter
WHERE  name IN ('processes', 'sessions', 'transactions')
ORDER BY name;

-- Check what is stored in SPFILE
SELECT name, value
FROM   v$spparameter
WHERE  name IN ('processes', 'sessions', 'transactions');

-- Check current usage against limits (the most important monitoring query)
SELECT resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions', 'transactions')
ORDER BY resource_name;

Setting the Parameter

PROCESSES is static and requires an instance restart. Always check the derived SESSIONS value after changing it.

-- Set PROCESSES to 500 (SESSIONS will auto-derive to 772 at restart)
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- If you also want explicit control over SESSIONS, set it too
-- Formula: SESSIONS = 1.5 * PROCESSES + 22
ALTER SYSTEM SET processes = 500  SCOPE = SPFILE;
ALTER SYSTEM SET sessions  = 772  SCOPE = SPFILE;  -- 1.5 * 500 + 22

-- Verify what will be used at next startup
SELECT name, value FROM v$spparameter
WHERE  name IN ('processes', 'sessions', 'transactions');

-- Shutdown and restart for changes to take effect
-- (Perform during a maintenance window)
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- After restart, confirm the new limits
SELECT resource_name, initial_allocation, limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

Tuning Guidance

Recommended Values

Environment	Typical Connections	PROCESSES Setting	Notes
Small development	< 50 users	150–200	Includes ~50 background processes
Small production OLTP	100–200 users	300–400	50% headroom over peak
Medium production OLTP	200–500 users	600–800	Add connection pool overhead
Large production OLTP	500–2000 users	1000–2500	Monitor closely with V$RESOURCE_LIMIT
Connection pooled (e.g., DRCP, pgBouncer)	Thousands of app threads	200–500	Pool reduces OS process count drastically
RAC node	Per-node connections	Per-node sizing	Each node has its own PROCESSES limit

Background process overhead: A typical Oracle instance uses 40–80 background processes depending on features enabled (Data Guard, Streams, Scheduler jobs, etc.). Always include this overhead in your calculation.

-- Count current background processes
SELECT COUNT(*) AS background_count
FROM   v$process
WHERE  background = 1;

-- Count all current processes
SELECT COUNT(*) AS total_processes
FROM   v$process;

-- Breakdown: background vs foreground
SELECT CASE WHEN background = 1 THEN 'Background' ELSE 'Foreground' END AS process_type,
       COUNT(*) AS process_count
FROM   v$process
GROUP BY background;

How to Size

Use observed peak usage as the baseline, not average usage. Connection spikes (application restarts, batch job windows, end-of-period processing) often exceed the daily average by 2–5x.

-- Step 1: Check current and historical peak utilization
SELECT resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value,
       ROUND(max_utilization / NULLIF(limit_value, 0) * 100, 1) AS peak_pct_of_limit
FROM   v$resource_limit
WHERE  resource_name = 'processes';

-- Step 2: List all active processes with session details
SELECT p.pid,
       p.spid         AS os_pid,
       p.background,
       p.program,
       s.username,
       s.status,
       s.machine,
       s.logon_time
FROM   v$process p
LEFT JOIN v$session s ON p.addr = s.paddr
ORDER BY p.background DESC, p.pid;

-- Step 3: Check trend from AWR (requires Diagnostics Pack)
SELECT snap_id,
       instance_number,
       num_sess                          AS sessions_at_snap
FROM   dba_hist_active_sess_history
GROUP BY snap_id, instance_number
ORDER BY snap_id;

-- Step 4: Size formula
-- Recommended PROCESSES = peak_max_utilization * 1.25 + background_processes
-- Round up to nearest 100 for clean administration
-- Example: peak sessions = 380, background = 55
--   PROCESSES = CEIL((380 + 55) * 1.25 / 100) * 100 = 600

Monitoring

Set up proactive monitoring to alert before the limit is reached. The cost of hitting PROCESSES in production is a complete connection outage.

-- Alert-ready query: processes usage percentage
SELECT resource_name,
       current_utilization                                   AS current,
       max_utilization                                       AS peak,
       limit_value                                          AS limit_val,
       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 = 'processes';
-- Alert if current_pct > 80% or peak_pct > 90%

-- Processes connected but idle for a long time (cleanup candidates)
SELECT p.spid    AS os_pid,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.program,
       ROUND((SYSDATE - s.last_call_et / 86400) * 1440) AS idle_minutes,
       s.logon_time
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.status  = 'INACTIVE'
AND    s.username IS NOT NULL
AND    s.last_call_et > 1800   -- idle more than 30 minutes
ORDER BY s.last_call_et DESC
FETCH FIRST 30 ROWS ONLY;

Common Issues

ORA-00020: Maximum number of processes exceeded

Occurs when a new connection attempt is made and the instance has reached the PROCESSES limit. No new connections — including DBA connections — can be established unless RESTRICTED SESSION mode is used.

Emergency diagnosis (if you can connect at all):

-- If SYSDBA connection is available (background processes are not counted against SESSIONS)
-- Connect: sqlplus / as sysdba

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

-- Find sessions to kill to free processes immediately
SELECT sid, serial#, username, machine, program, status, last_call_et
FROM   v$session
WHERE  username IS NOT NULL
AND    status   = 'INACTIVE'
ORDER BY last_call_et DESC
FETCH FIRST 20 ROWS ONLY;

-- Kill a session to free a process slot
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Permanent fix (requires restart):

-- Increase PROCESSES in SPFILE, then schedule a restart
ALTER SYSTEM SET processes = 600 SCOPE = SPFILE;
-- Plan and execute: SHUTDOWN IMMEDIATE; STARTUP;

See the full ORA-00020 guide.

OS-level process limit prevents increasing PROCESSES

On Linux, ulimit -u (max user processes) for the oracle OS user must be at least PROCESSES + 100. If the OS limit is too low, Oracle may fail to start or refuse to honor a higher PROCESSES value.

# Check current OS limits for the oracle user
su - oracle -c "ulimit -a" | grep "max user processes"

# Increase in /etc/security/limits.conf:
# oracle   soft   nproc   65536
# oracle   hard   nproc   65536

# Also check /etc/security/limits.d/oracle*.conf files

PROCESSES set too high wastes memory

Oracle pre-allocates the process state area (PSA) for each potential process slot. At very high values (e.g., PROCESSES = 10000) on a server with limited RAM, this can consume significant memory even when connections are sparse.

-- Check how much memory is allocated to process overhead
SELECT name, bytes / (1024 * 1024) AS mb
FROM   v$sgastat
WHERE  name LIKE '%fixed%'
OR     name LIKE '%process%'
ORDER BY bytes DESC;

Parameter	Relationship
`SESSIONS`	Derived from `PROCESSES` at startup: `1.5 * PROCESSES + 22`; can be set explicitly
`TRANSACTIONS`	Derived from `SESSIONS`: `1.1 * SESSIONS`; governs undo segment allocation
`LICENSE_MAX_SESSIONS`	Soft session limit for license compliance (separate from PROCESSES)
`LICENSE_MAX_USERS`	Maximum named users for license compliance
`DISPATCHERS`	Shared server mode; reduces OS processes needed per user connection

Version Notes

Version	Notes
All versions	Static parameter; restart always required
10g+	Background process count increases with features (Streams, Scheduler, etc.)
11gR2+	Typically 50–80 background processes on a standard instance
12c	PDB connections all count against CDB-level PROCESSES
19c	Multitenant with many PDBs can significantly increase background process count
21c+	No functional change; monitor per-PDB session limits separately from PROCESSES