Home / OCI

OCITroubleshootingAdvanced

Sev1: Autonomous Database refusing connections — ORA-00018 sessions exhausted or CPU saturated

Restore service when Autonomous Database hits its session limit or runs at 100% CPU. Find the sessions and SQL responsible, kill safely, scale ECPUs or enable auto scaling, and fix connection pools and service choices.

Deepak Bhardwaj2 min read

Applies toOracle Autonomous Database Serverless (ATP, ADW) on OCI, ECPU model, OCI CLI 3.x

Symptoms

  • ORA-00018: maximum number of sessions exceeded or ORA-12516/ORA-12520 listener refusals.
  • Queries queue; the Performance Hub shows CPU at the limit and many sessions waiting on resmgr:cpu quantum.

1. Who's connected?

Connect as ADMIN (keep a session open during incidents, or use Database Actions SQL worksheet):

SELECT machine, program, username, service_name, status, COUNT(*) sessions
FROM v$session
WHERE type = 'USER'
GROUP BY machine, program, username, service_name, status
ORDER BY sessions DESC FETCH FIRST 15 ROWS ONLY;

A single app server with hundreds of INACTIVE sessions = connection pool leak or a pool max set too high.

2. What's burning CPU?

SELECT sql_id, ROUND(SUM(cpu_time)/1e6) cpu_s, SUM(executions) execs, MAX(SUBSTR(sql_text,1,80)) sql
FROM v$sql
WHERE last_active_time > SYSDATE - 1/24
GROUP BY sql_id ORDER BY cpu_s DESC FETCH FIRST 10 ROWS ONLY;

Performance Hub (console → database → Performance Hub) shows the same with ASH by SQL ID, session and wait class.

3. Immediate relief

Kill runaway or leaked sessions:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM v$session
WHERE machine = 'app-batch-01' AND status = 'INACTIVE' AND last_call_et > 1800;

Review the generated statements, then run them.

Scale compute (online):

oci db autonomous-database get --autonomous-database-id $ADB \
  --query 'data.{ecpu:"compute-count", auto:"is-auto-scaling-enabled", state:"lifecycle-state"}'

oci db autonomous-database update --autonomous-database-id $ADB --compute-count 8 --is-auto-scaling-enabled true

Session limits scale with the compute you provision, so scaling up also raises the ceiling. Check the documented session/concurrency limits per ECPU and per service.

Note

Auto scaling lets the database use up to three times the base ECPU count when needed; you pay for what is used. It's the quickest protection against bursts.

4. Use the right service

Service Use for Concurrency
tpurgent Highest priority short transactions (ATP) Manual parallelism
tp OLTP application traffic Highest concurrency
high / medium Reports, batch, parallel queries Lower concurrency
low Background, ad-hoc Highest concurrency, lowest priority

Batch jobs connected with high can take most CPU and starve OLTP. Move them to medium/low, or schedule them off-peak.

5. Fix connection pools

  • Set pool max per app server so total across servers stays well under the limit.
  • Enable validation and idle timeouts so dead connections are returned.
  • Consider Database Resident Connection Pooling (DRCP) for many short-lived clients if your app supports it.

Prevent it next time

  • Alarms on CpuUtilization and Sessions metrics (oci_autonomous_database namespace).
  • Auto scaling on for production.
  • A per-application connection budget agreed with each team.

Related fixes