Thursday, December 05, 2013

ORA-00018 and ORA-02095

A user complained about ORA-00018 errors when trying to connect to an Oracle 11.1 database from PL/SQL Developer. I checked in the alert log and saw the following message several times:
Mon Nov 25 10:11:35 2013
Errors in file /oracle/app/oracle/product/diag/rdbms/nbacog3/NBACOG3/trace/NBACOG3_m001_11242.trc:
ORA-00018: maximum number of sessions exceeded
Mon Nov 25 10:11:46 2013
 
I looked in the database’s initialization parameter file and saw the line below:
 
sessions=75
 
I checked in the database itself but the value was slightly larger. I think the sessions parameter may sometimes be recalculated from other parameters:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'sessions'
SQL> /
 
VALUE
----------
87
 
SQL>
 
I tried to increase it dynamically but could not do so:
 
SQL> l
  1* alter system set processes = 200
SQL> /
alter system set processes = 200
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot
be modified
 
SQL>
 
I closed the database and amended the line in the parameter file as follows:
 
sessions=200
 
Then I opened the database and the new value had taken effect:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'sessions'
SQL> /
 
VALUE
----------
200
 
SQL>

No comments: