ORA-00018 maximum number of sessions exceeded
Cause: All session state objects are in used.
Action: Increase the value of the SESSIONS initialization parameter.
ORA-00018 comes under Oracle Database Server Messages. These messages are generated
by the Oracle database server when running any Oracle program.
How to increase SESSION initialization parameter ? Here Given it below
1. Login as sysdba
C:\>sqlplus /nolog
SQL> conn / 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 immediate;
If Database shutdown taken longtime then you can apply shutdown abort command
SQL> shutdown abort
SQL> startup
After successful database open then again check the above modified parameter value are update yes or not. If parameter value are updated then create new pfile on oracle home pfile location for last updated modified oracle initialization parameter .
5. Create pfile on oracle home pfile location
SQL> create pfile ='d:\pfilelocation' from spfile;
Enjoy.........
