ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

How to increase PROCESSES initialization parameter:

1.Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters:-

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     247
shared_server_sessions               integer


SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150

SQL> show parameter transactions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     271
transactions_per_rollback_segment    integer     5

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 restart the instance.
SQL> alter system set processes=400 scope=spfile;

System altered.

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

System altered.

SQL> alter system set transactions=490 scope=spfile;

System altered.


Done..

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...