ORA-00018: Maximum Number of Sessions Exceeded – Causes and Solutions

Understanding ORA-00018 Error in Oracle Databases

The ORA-00018: maximum number of sessions exceeded error is a common issue encountered in Oracle databases, particularly in high-traffic environments. This error occurs when the number of active database sessions surpasses the configured limit, preventing new connections and potentially disrupting application performance.

To effectively resolve and prevent this issue, it is crucial to understand its causes, solutions, and best practices for managing Oracle database sessions efficiently.

Causes of ORA-00018: Maximum Number of Sessions Exceeded

This can happen if there are too many users trying to connect to the database at the same time, or if a single user is running a long-running query or transaction.

1. Insufficient SESSIONS Parameter Value

The SESSIONS initialization parameter in Oracle determines the maximum number of concurrent sessions allowed. If this value is set too low, users may frequently experience the ORA-00018 error.

2. High Number of Idle or Inactive Sessions

Idle sessions can accumulate over time, consuming valuable database resources. Poorly managed application connections can result in numerous unused sessions, eventually exceeding the session limit.

3. Poor Connection Pooling Configuration

Applications that connect to Oracle databases often use connection pooling to manage database connections efficiently. Misconfigured connection pooling can lead to excessive session usage.

4. Long-Running Queries or Transactions

Queries that take an extended time to execute can hold database sessions for longer durations, preventing new connections and leading to session exhaustion.

5. Background Processes Consuming Sessions

Oracle’s background processes, such as DBMS Jobs, scheduler jobs, and system monitoring processes, also consume database sessions. If not properly accounted for, they can contribute to the ORA-00018 error.

6. Application Bugs or Leaks

Some applications may fail to properly release database connections after use, resulting in a gradual increase in session count until the limit is reached.

How to Fix ORA-00018: Maximum Number of Sessions Exceeded

To solve this problem, you need to increase the sessions parameters. Sessions parameters depend on processes and transactions parameters.

1- Increase the SESSIONS Parameter Value

Check the v$session view to see a list of all the current sessions in the database. This can help you identify which sessions are using the most resources and may be causing the error.

SHOW PARAMETER SESSIONS;

From Above query used by V$SESSION that The sessions parameter is 170 while count shows only 163 sessions are running. the difference is of 7 sessions left. which shows something wrong. If the current value is insufficient, modify it using:

ALTER SYSTEM SET SESSIONS = 500 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

This change requires a database restart to take effect.

2. Monitor and Terminate Idle Sessions

Check the v$resource_limit view to see the current maximum number of sessions allowed by the database. This can help you verify that the SESSIONS parameter has been set correctly.
Identifying and terminating idle sessions can free up resources. Use the following query to find inactive sessions:

SELECT SID, SERIAL#, STATUS, MACHINE, PROGRAM FROM V$SESSION WHERE STATUS = 'INACTIVE';

Terminate an idle session with:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

3. Identify and Tune Long-Running Queries

Monitor long-running queries that could be holding database sessions for extended periods:

SELECT SID, SERIAL#, SQL_ID, ELAPSED_TIME/1000000 AS ELAPSED_SECONDS FROM V$SESSION JOIN V$SQL USING(SQL_ID) WHERE STATUS = 'ACTIVE' ORDER BY ELAPSED_TIME DESC;

4. Implement Session Timeout Policies

Configuring automatic session timeouts ensures that inactive sessions do not remain open indefinitely. Modify PROFILE settings to enforce session timeouts:

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30;

5. Adjust PROCESSES Parameter if Necessary

The SESSIONS parameter depends on the PROCESSES parameter. If PROCESSES is too low, increasing SESSIONS alone may not be sufficient. Check the current PROCESSES value:

SHOW PARAMETER PROCESSES;

increase, if needed

ALTER SYSTEM SET PROCESSES = 600 SCOPE=SPFILE;

Best Practices to Prevent ORA-00018 Error

To avoid hitting the session limit in the future, consider the following best practices:

1. Regularly Monitor Session Usage

Schedule jobs or scripts to monitor active sessions:

SELECT COUNT(*) FROM V$SESSION;

Set up alerts to notify administrators when the session count reaches a critical threshold.

2. Enforce Proper Connection Handling in Applications

Ensure that applications properly close connections when not in use. Use connection pooling effectively to limit the number of active sessions.

3. Implement Resource Governor Policies

Use Oracle Database Resource Manager to allocate and restrict session usage per application or user group.

4. Conduct Regular Performance Tuning

Optimize SQL queries and database indexing to reduce session hold times and improve efficiency.

5. Schedule Database Maintenance and Cleanup

Perform regular audits to identify and terminate unnecessary sessions, especially those from stale connections.

6. Ready to Restart database services

Once you have changed the value of the SESSIONS parameter, you will need to save the configuration file and restart the database.

After the database has restarted, you should be able to connect to the database without receiving the ORA-00018 error.

SQL> shutdown immediate
SQL> startup

Conclusion

The ORA-00018: maximum number of sessions exceeded error can cause significant disruptions in Oracle database environments. However, by understanding the root causes and implementing effective session management strategies, you can prevent and resolve this issue efficiently. Proactive monitoring, proper configuration, and optimized connection handling are key to maintaining a healthy and stable Oracle database system.

It’s important to note that modifying database parameters and terminating sessions should be performed carefully and with proper understanding in order to resolve error ORA-00018-Maximum number of sessions exceeded. If you’re unsure or need further assistance, it’s recommended to consult with a database administrator or Oracle support.