Question: What do
I do when my database refuses to accept new connections and
I get the ORA-00018 error in my alert log:
ORA-00018 maximum number of sessions
exceeded
I do not see a sessions parameter so I don't know how to
address the ORA-00018 error.
Answer: The oerr utility shows
this for the ORA-0018 error, indicating that the fix is to
increase the "processes" initialization parameter.
To fix the ORA-00018 and the
ORA-00020, we
increase the processes parameter, usually doubling the
processes value to allow for future database growth.
ORA-00018 maximum
number of sessions exceeded.
Cause:
All session state objects are in use.
Action:
Increase the value of the SESSIONS initialization parameter.
The processes parameter also has
"derivative" parameters that are controlled by the value of
the processes parameter. These derived parameters
include sessions,
transactions, enqueue_resources,
and
_enqueue_hash_chains. The values for sessions and transactions are computed
from the value of the processes parameter:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
To remove the ORA-00018 we want to increase the processes
parameter and then bounce the instance.
SQL> show
parameter processes
SQL> alter system set processes=2000
scope=spfile;
SQL> shutdown abort
SQL> startup
You should set the processes
parameter to a very high value because it consumers very
little resources and you do not want your instance to "lock
up" with the ORA-0018 error.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
