ORA-00020: Maximum Number of Processes Exceeded

ORA-00020: Maximum Number of Processes Exceeded

Error Description

ORA-00020 occurs when the Oracle database has reached the maximum number of processes defined by the PROCESSES initialization parameter. This prevents new connections from being established and can severely impact database availability.

Complete Error Message

ORA-00020: maximum number of processes (N) exceeded

Where N is the current value of the PROCESSES parameter.

Severity Level

🔴 CRITICAL - Prevents new database connections and can cause application failures.

Common Causes

1. Application Connection Issues

• Connection pools not properly configured
• Applications not closing connections properly
• Connection leaks in application code
• Runaway processes creating excessive connections

2. Configuration Issues

• PROCESSES parameter set too low for workload
• Inadequate monitoring of process usage
• Improper sizing during initial setup
• Growth in application usage without parameter adjustment

3. System Issues

• Background process failures causing accumulation
• Long-running transactions holding processes
• Database hanging causing process buildup
• External factors preventing process cleanup

4. Administrative Operations

• Large parallel operations consuming processes
• Import/export operations with high parallelism
• Administrative scripts creating multiple connections
• Backup operations with excessive parallelism

Immediate Diagnostic Steps

1. Check Current Process Usage

-- Check current process count vs limit
SELECT
  'Current Processes' as metric,
  COUNT(*) as current_value,
  (SELECT value FROM v$parameter WHERE name = 'processes') as limit_value,
  ROUND(COUNT(*) / (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'processes') * 100, 2) as pct_used
FROM v$process
UNION ALL
SELECT
  'Current Sessions' as metric,
  COUNT(*) as current_value,
  (SELECT value FROM v$parameter WHERE name = 'sessions') as limit_value,
  ROUND(COUNT(*) / (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'sessions') * 100, 2) as pct_used
FROM v$session;

-- Show process breakdown by type
SELECT
  CASE
    WHEN program LIKE '%oracle%' THEN 'Oracle Background'
    WHEN program LIKE '%TNS%' THEN 'User Connection'
    WHEN program IS NULL THEN 'Background Process'
    ELSE 'Other'
  END as process_type,
  COUNT(*) as count
FROM v$process
GROUP BY
  CASE
    WHEN program LIKE '%oracle%' THEN 'Oracle Background'
    WHEN program LIKE '%TNS%' THEN 'User Connection'
    WHEN program IS NULL THEN 'Background Process'
    ELSE 'Other'
  END
ORDER BY count DESC;

2. Identify Top Process Consumers

-- Find users with most processes
SELECT
  username,
  machine,
  program,
  COUNT(*) as session_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, machine, program
ORDER BY session_count DESC;

-- Check for long-running sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.machine,
  s.program,
  s.status,
  s.logon_time,
  ROUND((SYSDATE - s.logon_time) * 24, 2) as hours_connected,
  s.last_call_et/3600 as hours_since_last_call
FROM v$session s
WHERE s.username IS NOT NULL
ORDER BY s.logon_time;

3. Check for Blocked or Hanging Sessions

-- Find blocking sessions
SELECT
  blocking_session,
  sid,
  serial#,
  username,
  machine,
  program,
  wait_class,
  event,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

-- Check for sessions waiting on locks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.machine,
  s.event,
  s.seconds_in_wait,
  s.state
FROM v$session s
WHERE s.event LIKE '%lock%'
   OR s.event LIKE '%latch%'
   OR s.event LIKE '%enq%'
ORDER BY s.seconds_in_wait DESC;

Immediate Solutions

1. Emergency Process Cleanup

-- Kill idle sessions (use with caution)
-- First, identify idle sessions
SELECT
  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' as kill_command,
  username,
  machine,
  program,
  status,
  last_call_et/3600 as idle_hours
FROM v$session
WHERE status = 'INACTIVE'
  AND username IS NOT NULL
  AND last_call_et > 7200  -- Idle for more than 2 hours
  AND username NOT IN ('SYS', 'SYSTEM')  -- Protect system users
ORDER BY last_call_et DESC;

-- Execute kill commands for long-idle sessions (review first!)
-- Example (execute only after review):
-- ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

2. Temporary Process Limit Increase

-- Check current setting
SELECT name, value, description
FROM v$parameter
WHERE name = 'processes';

-- Calculate recommended increase
SELECT
  'Current processes parameter: ' || value as current_setting,
  'Current usage: ' || (SELECT COUNT(*) FROM v$process) as current_usage,
  'Recommended setting: ' || CEIL((SELECT COUNT(*) FROM v$process) * 1.5) as recommended
FROM v$parameter
WHERE name = 'processes';

-- Increase processes parameter (requires restart)
-- First, update in SPFILE
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- Sessions parameter is automatically calculated as processes * 1.1 + 5
-- But you can set it explicitly if needed
ALTER SYSTEM SET sessions = 555 SCOPE = SPFILE;

-- Restart database to apply changes
SHUTDOWN IMMEDIATE;
STARTUP;

3. Kill Specific Problem Sessions

-- Identify and kill runaway processes
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.machine,
  s.program,
  p.spid as os_process_id,
  s.logon_time,
  s.last_call_et
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND s.username IS NOT NULL
  AND (
    s.last_call_et > 86400  -- Idle more than 24 hours
    OR s.program LIKE '%runaway%'  -- Known problematic programs
  )
ORDER BY s.last_call_et DESC;

-- Kill sessions identified above
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Long-term Solutions

1. Proper Process Parameter Sizing

-- Calculate optimal processes parameter based on workload
WITH process_stats AS (
  SELECT
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24') as sample_hour,
    COUNT(DISTINCT session_id) as concurrent_sessions
  FROM v$active_session_history
  WHERE sample_time >= SYSDATE - 7  -- Last 7 days
  GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24')
),
process_recommendations AS (
  SELECT
    MAX(concurrent_sessions) as peak_sessions,
    ROUND(AVG(concurrent_sessions), 0) as avg_sessions,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY concurrent_sessions), 0) as p95_sessions
  FROM process_stats
)
SELECT
  peak_sessions,
  avg_sessions,
  p95_sessions,
  -- Recommended processes = 95th percentile + 50% buffer + background processes
  CEIL(p95_sessions * 1.5) + 50 as recommended_processes
FROM process_recommendations;

-- Set the recommended value
-- ALTER SYSTEM SET processes = [recommended_value] SCOPE = SPFILE;

2. Connection Pool Optimization

-- Monitor connection patterns to optimize pools
SELECT
  username,
  machine,
  program,
  COUNT(*) as active_connections,
  MIN(logon_time) as first_connection,
  MAX(logon_time) as latest_connection,
  ROUND(AVG(last_call_et), 2) as avg_idle_seconds
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, machine, program
ORDER BY active_connections DESC;

-- Create view for connection monitoring
CREATE OR REPLACE VIEW connection_summary AS
SELECT
  TRUNC(logon_time, 'HH') as connection_hour,
  username,
  machine,
  COUNT(*) as connection_count,
  AVG(last_call_et) as avg_idle_time
FROM v$session
WHERE username IS NOT NULL
  AND logon_time >= SYSDATE - 1
GROUP BY TRUNC(logon_time, 'HH'), username, machine
ORDER BY connection_hour DESC, connection_count DESC;

3. Implement Connection Monitoring

-- Create process monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_process_usage AS
  v_current_processes NUMBER;
  v_max_processes NUMBER;
  v_pct_used NUMBER;
  v_threshold NUMBER := 85;  -- Alert at 85% usage
BEGIN
  SELECT COUNT(*) INTO v_current_processes FROM v$process;
  SELECT TO_NUMBER(value) INTO v_max_processes FROM v$parameter WHERE name = 'processes';

  v_pct_used := ROUND((v_current_processes / v_max_processes) * 100, 2);

  IF v_pct_used >= v_threshold THEN
    -- Log warning
    INSERT INTO process_alerts (alert_time, current_processes, max_processes, pct_used)
    VALUES (SYSDATE, v_current_processes, v_max_processes, v_pct_used);

    -- Write to alert log
    EXECUTE IMMEDIATE 'BEGIN DBMS_SYSTEM.KSDWRT(2, ''WARNING: Process usage at '' || :1 || ''%''); END;'
    USING v_pct_used;
  END IF;

  COMMIT;
END;
/

-- Schedule monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'process_monitor_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN monitor_process_usage; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE
  );
END;
/

Prevention Strategies

1. Application Best Practices

// Java connection pool configuration example
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl");
config.setUsername("app_user");
config.setPassword("password");

// Connection pool sizing
config.setMaximumPoolSize(20);          // Max connections
config.setMinimumIdle(5);               // Min idle connections
config.setConnectionTimeout(30000);     // 30 seconds
config.setIdleTimeout(600000);          // 10 minutes
config.setMaxLifetime(1800000);         // 30 minutes

// Connection validation
config.setConnectionTestQuery("SELECT 1 FROM DUAL");
config.setValidationTimeout(5000);

HikariDataSource dataSource = new HikariDataSource(config);

2. Database Configuration

-- Configure resource limits
ALTER PROFILE DEFAULT LIMIT
  SESSIONS_PER_USER 10
  CONNECT_TIME 480  -- 8 hours
  IDLE_TIME 60;     -- 1 hour

-- Set up automatic session cleanup
ALTER SYSTEM SET resource_limit = TRUE;

-- Configure dead connection detection
-- Add to sqlnet.ora
-- SQLNET.EXPIRE_TIME = 10
-- DCD.ENABLE = TRUE

3. Monitoring and Alerting

-- Create comprehensive monitoring view
CREATE OR REPLACE VIEW v_process_monitoring AS
SELECT
  'Current Usage' as metric,
  (SELECT COUNT(*) FROM v$process) as current_value,
  (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'processes') as limit_value,
  ROUND((SELECT COUNT(*) FROM v$process) /
        (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'processes') * 100, 2) as pct_used
FROM dual
UNION ALL
SELECT
  'Active Sessions' as metric,
  (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') as current_value,
  (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'sessions') as limit_value,
  ROUND((SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') /
        (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'sessions') * 100, 2) as pct_used
FROM dual;

-- Regular monitoring query
SELECT * FROM v_process_monitoring;

Troubleshooting Tools

1. Process Analysis Script

-- Comprehensive process analysis
SELECT
  'Process Summary' as section,
  'Total Processes: ' || COUNT(*) as info
FROM v$process
UNION ALL
SELECT
  'Session Summary' as section,
  'Active Sessions: ' || COUNT(*) as info
FROM v$session WHERE status = 'ACTIVE'
UNION ALL
SELECT
  'Connection Summary' as section,
  'User Connections: ' || COUNT(*) as info
FROM v$session WHERE username IS NOT NULL
UNION ALL
SELECT
  'Parameter Info' as section,
  'Max Processes: ' || value as info
FROM v$parameter WHERE name = 'processes'
UNION ALL
SELECT
  'Parameter Info' as section,
  'Max Sessions: ' || value as info
FROM v$parameter WHERE name = 'sessions';

2. Session Cleanup Script

-- Create safe session cleanup procedure
CREATE OR REPLACE PROCEDURE cleanup_idle_sessions(
  p_idle_hours NUMBER DEFAULT 4,
  p_exclude_users VARCHAR2 DEFAULT 'SYS,SYSTEM,DBSNMP'
) AS
  v_kill_count NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT sid, serial#, username, machine, last_call_et
    FROM v$session
    WHERE status = 'INACTIVE'
      AND username IS NOT NULL
      AND last_call_et > p_idle_hours * 3600
      AND username NOT IN (
        SELECT TRIM(REGEXP_SUBSTR(p_exclude_users, '[^,]+', 1, LEVEL))
        FROM dual
        CONNECT BY REGEXP_SUBSTR(p_exclude_users, '[^,]+', 1, LEVEL) IS NOT NULL
      )
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
      v_kill_count := v_kill_count + 1;

      DBMS_OUTPUT.PUT_LINE('Killed session: ' || rec.username || '@' || rec.machine ||
                          ' (idle ' || ROUND(rec.last_call_et/3600, 1) || ' hours)');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed to kill session ' || rec.sid || ': ' || SQLERRM);
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Total sessions killed: ' || v_kill_count);
END;
/

Related Oracle Errors

• ORA-00018: Maximum Number of Sessions Exceeded - Session limit issues
• ORA-03113: End-of-file on Communication - Connection drops
• ORA-12560: TNS Protocol Adapter Error - Connection failures
• ORA-04030: Out of Process Memory - Memory-related process issues

Best Practices

1. Size PROCESSES parameter appropriately based on workload analysis
2. Implement connection pooling in applications
3. Monitor process usage regularly with automated alerts
4. Configure resource limits to prevent runaway sessions
5. Use connection validation in application pools
6. Implement session cleanup procedures for idle connections
7. Test connection handling under load conditions
8. Document process management procedures for operations teams

Emergency Response Checklist

1. ✅ Assess impact - Can new connections be made?
2. ✅ Check current usage - How close to limit are we?
3. ✅ Identify problem sessions - Find idle or blocking sessions
4. ✅ Kill problematic sessions carefully (avoid system sessions)
5. ✅ Increase process limit if needed (requires restart)
6. ✅ Fix root cause - Application connection leaks, etc.
7. ✅ Implement monitoring to prevent recurrence
8. ✅ Document incident and lessons learned

This error requires immediate attention as it prevents new database connections. The key is identifying and cleaning up unnecessary processes while implementing long-term process management strategies.