Why can’t I set oracle sessions value to 35

oracleoracle-11gparametersession

I want to simulate the error "ORA-00018.Maximum number of sessions exceed", so I want to limit the number of sessions to 35, and open serverl sqlplus to simulate this error, but sessions number didn't change 

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

change sessions from 248 to 35

SQL> alter system set sessions=35 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

restart oracle instance

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

Nothing changed

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

Can anyone tell me why I can't change this value to 35, and how to simulate the "ORA-00018.Maximum number of sessions exceed" error?

Best Answer

SESSIONS

Default value Derived: (1.5 * PROCESSES) + 22

...

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

If you want to decrease sessions, you need to decrease processes as well. Still, if you decrease processes, you will have plenty of 'free' sessions, and you will need to open several sessions until you receive the error.

Easiest way to simulate the error without messing with processes and opening several connections:

alter system set dispatchers = '(PROTOCOL=TCP)';
alter system set shared_servers = 1;
alter system set shared_server_sessions = 0;

$ sqlplus user/password@\'127.0.0.1:1521/orcl:shared\'

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 21 11:38:41 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-00018: maximum number of sessions exceeded
Related Question