Managing sessions in Oracle is like managing seats in a bus: if all seats are taken, no new passenger can get in. Similarly, when your Oracle database hits its session limit, new connections are blocked, and you end up with one of the most common and frustrating Oracle errors:
ORA-00018: Maximum number of sessions exceeded
This error frequently appears in production systems, especially in high-traffic environments, unstable application servers, or when session management is poorly tuned. The good news? ORA-00018 is very easy to diagnose and fix once you know what’s causing it.
In this guide, we break down the error in plain English, show real reasons why it happens, and walk you through step-by-step solutions that DBAs use in live environments.
What ORA-00018 Really Means
Every Oracle database has limits such as:
- Maximum number of processes (
processes) - Maximum number of sessions (
sessions)
A session is simply a connection to the database — from an app, script, API, SQL Developer, or the OS.
When the number of sessions reaches the configured maximum, Oracle stops allowing new connections. That’s when you see:
ORA-00018: Maximum number of sessions exceeded
It means:
- All allowed sessions are used
- New connections cannot be created
- Some application users may experience downtime
- Your database may already be under stress
Why Does ORA-00018 Occur? (Real-World Causes)
Here are the MOST common reasons this error appears:
1. Application not closing sessions
Apps may:
- Open sessions but not close them
- Leak connections
- Create hundreds of idle sessions
2. processes and sessions parameters set too low
Default values in small systems:
processes = 150
sessions = 248 (calculated automatically)
Modern apps need much more.
3. Background processes consumed the total session count
Oracle itself consumes sessions for:
- JOB queues
- Data Guard
- MMON / SMON / PMON
- AQ processes
4. Connection pool misconfiguration
Common in:
- WebLogic
- Tomcat
- OAS
- Microservices
They aggressively open connections without releasing them.
5. Sudden traffic spike
Peak hours or batch jobs can overload the system.
6. Idle session buildup
Developers leave SQL Developer open overnight — and those sessions accumulate.
How to Diagnose ORA-00018 (Step-by-Step)
1. Check current sessions
SELECT COUNT(*) FROM v$session;
2. Check session limit
SHOW PARAMETER sessions;
3. Check processes parameter
SHOW PARAMETER processes;
4. Identify users with too many sessions
SELECT username, COUNT(*)
FROM v$session
GROUP BY username
ORDER BY 2 DESC;
5. Identify idle sessions
SELECT sid, serial#, username, status
FROM v$session
WHERE status = 'INACTIVE';
6. View top programs causing session load
SELECT program, COUNT(*)
FROM v$session
GROUP BY program
ORDER BY 2 DESC;
How to Fix ORA-00018 — Full Solutions
Solution 1: Increase SESSIONS and PROCESSES
The most common fix is to increase:
If you are planning to increase “sessions” parameter you should also plan to increase “processes and “transactions” parameters.
Processes=x
Sessions=x1.1+5
Transactions=sessions1.1
ALTER SYSTEM SET processes = 500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions = 800 SCOPE=SPFILE;
NOTE:
You must restart the database for these changes to take effect.
Restart:
SHUTDOWN IMMEDIATE;
STARTUP;
Solution 2: Kill Idle or Hanging Sessions
Find sessions:
SELECT sid, serial#, username
FROM v$session
WHERE status='INACTIVE';
Kill session:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Solution 3: Fix the Application Connection Pool
Often the REAL fix is on the application side.
Checklist:
- Reduce max pool size
- Enable connection timeout
- Force idle connection cleanup
- Implement proper session release
WebLogic example:
- Max capacity: Reduce from 200 → 60
- Shrink frequency: 15 minutes
- Test on reserve: Enabled
Solution 4: Reduce background process overload
Too many jobs or AQ processes? Check:
SELECT * FROM dba_scheduler_jobs;
Disable unused jobs:
BEGIN
DBMS_SCHEDULER.DISABLE('JOB_NAME');
END;
/
Solution 5: Fix session leaking code (developer issue)
Common mistakes:
❌ Opening session inside loop
❌ Not closing session on exceptions
❌ Poor JDBC pool implementation
Best practice:
try {
Connection conn = datasource.getConnection();
...
} finally {
conn.close();
}
Solution 6: Temporary emergency fix
If you’re locked out, use sysdba locally:
sqlplus / as sysdba
Kill old sessions:
SELECT sid, serial# FROM v$session WHERE username IS NOT NULL;
Then kill:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This gives temporary breathing room.
How to Prevent ORA-00018 in Production
Here are the best practices every DBA should follow:
✔ Increase PROCESSES and SESSIONS to modern values
Example recommended baseline:
processes = 1000
sessions = 1500
✔ Enforce connection pool limits
Never allow unlimited connections.
✔ Auto-kill idle sessions
Many companies set:
IDLE_TIME = 30
✔ Monitor with queries & alerts
Tools like:
- OEM Cloud Control
- Grafana
- Prometheus
- Dynatrace
✔ Weekly DBA session audits
Especially for critical apps.
Final Thoughts
The ORA-00018: Maximum Number of Sessions Exceeded error indicates your Oracle database has reached its connection limits. While the error seems severe, the solutions are straightforward once you identify the cause.
Whether it’s:
- increasing session limits,
- fixing app-side leaks,
- tuning connection pools, or
- clearing idle sessions,
you now have all the tools to resolve ORA-00018 cleanly and prevent it from coming back.
