ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded (Easy Step)

  

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

 Where Oracle level may not help?

Though, using ‘Alter system kill ‘ is considered to be preferable option to be at safer side, there are still some scenarios and some valid reasons to go a step ahead – Killing the sessions at OS level

- While we face the ORA-00018 maximum number of sessions exceeded error, it’s possible to kill session using the ‘Alter system’ command only if you already are connected to the database.
If not, database won’t allow the new connection, so there is the way to kill the session at OS level. To find the SPID without logging into database some Quick-slice (Microsoft) & likewise utilities are provided.
- Another reason is releasing the locks. The alter system may not release the locks at times. Though generally it does, it may hold it back till the connected session times out or the user tries to reconnect the session to discover it’s been killed (and the session actually disappears from the session view, from the “KILLED” status)

Immediate Action

At Operating System level –

UNIX – 

Unlike Windows, UNIX is process based. The sessions can be listed down as individual processes by ps command.

Then by using ‘kill -9 ‘command the related process/session can be killed

Syntax – 

 ps –ef | grep LOCAL_NO

We will get pid After that we can kill .

$ Kill -9 spid 

The session will be killed immediately and all resources will be released.

At Oracle Level –

The sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION command.

Firstly we will check active/inactive Session Status..
Generally We will find more inactive session is idle. we can kill those session..
Below command we can check status of session and machine name from where more sessions are coming.

select count(sid) " Total number of session",USERNAME,STATUS,MACHINE from gv$session where username is not null group by username,status,machine;



Now we are killing session.

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE' and username is not null;

or 

 we can use use below command to kill session.

Syntax-

ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’ 

This command will kill the session uniquely identified by the specified SID & serial number combo.
Most of the times, we won’t    find the session count decreased even after killing the sessions. Then the newly discovered thing by you will be the killed sessions holding the “KILLED” status.
The status will remain the same until timed out or till the user tries to reconnect the same session.

How to increase SESSION initialization parameter:

1. Login as sysdba

 sqlplus / as sysdba

2. Check Current Setting of Parameters

 sql> show parameter sessions

 sql> show parameter processes

 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase "processes and "transactions" parameters.

A basic formula for determining  these parameter values is as follows:

  processes=x

  sessions=x*1.1+5

  transactions=sessions*1.1

 

4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.

 sql> alter system set processes=500 scope=spfile;

 sql> alter system set sessions=555 scope=spfile;

 sql> alter system set transactions=610 scope=spfile;

 sql> shutdown abort

 sql> startup