Increasing Processes, Sessions and Transactions in Oracle XE
by Andrew Freemantle • June 22, 2009 • 28 Comments
Out of the box, Oracle Datatbase 10g Express Edition RDBMS is fast and powerful. The stated limitations of 2GB of maximum RAM usage and 2GB of total datafile management are plentiful for it to easily run as the back-end for a small to medium-sized office application. However, we soon hit a connection limit as characterised by the following Oracle Errors:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
ORA-00020: maximum number of processes (%s) exceeded
We can get this second message because Oracle creates Operating System processes to handle Connections (or Sessions) – which means Processes, Sessions (and as we’ll soon see..) Transactions are all related.
The default values in Oracle XE for these parameters are:
- Processes =40
- Sessions = 49
- Transactions = 53
I was able to generate the above error message (ORA-00020) from about ~30 connections on a vanilla Oracle XE installation (on Windows 7).
So, let’s increase these limits to allow more connections to our Oracle Server..
1. Log in as SYSDBA
From the menu ‘Oracle Database 10g Express Edition’, find and select ‘Run SQL Command Line’, then type:
|
1 |
connect sys as sysdba |
and enter your SYS, or SYSTEM password at the prompt
2. ALTER SYSTEM commands
Update: The Oracle XE 10g documentation links were broken, so they now point to the Oracle 11g R2 Standard Edition documentation instead – note that the default values are greater in 11g R2 Standard than the Express Edition (XE).
The Oracle Documentation states that TRANSACTIONS is derived from SESSIONS, which in turn is derived from PROCESSES, thus:
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS
So, what to start with for PROCESSES? Trebling it is as good a start as any, then I’d add a few more for good measure.. Here are the values I recommend:
- PROCESSES = 150
- SESSIONS = 300
- TRANSACTIONS = 330
type the following commands:
|
1 2 3 4 5 |
alter system set processes = 150 scope = spfile; alter system set sessions = 300 scope = spfile; alter system set transactions = 330 scope = spfile; |
then to make the settings take effect, we need to bounce the database..
|
1 2 3 |
shutdown immediate; startup; |
3. Verify the new parameters
with this simple select statement..
|
1 2 3 |
select name, value from v$parameter where name in ('processes', 'sessions', 'transactions'); |
And we’re done – a free, light and powerful Oracle RDBMS that’s able to serve more connections
Thanks, this is straightforward and working !
Now up and running with parallel unit tests, still failing, but for good reasons !
thanks a lot
you really help me
but are this values the max we can choose for oracle express ?
processes = 150
sessions = 300
transactions = 330
!
Hi Hassene, I’m glad it helped you
While there will be an answer to your question of what are the maximum values for Oracle Express, there are a few key factors I’d need to know first:
1. What Operating System are you running it on?
2. Is the Operating System 32bit or 64bit (although Oracle XE is 32bit only, this still affects how many processes the OS can handle in total)
3. How much RAM is available to the Operating System
Thanks for this. Whenever I need to install XE, I go to this site afterwards to change the settings (fatlemon is so easy to remember
)
Thank you very much, you save me today
!
Hi Andrew,
I altered the sessions to 150, but everytime I restart the xe database it defaults to 49. Do I have to configure the pfile manually if I want to store it permanently?
Thanks
@Sram – you shouldn’t have to edit the pfile manually to get these values to save permanently – that’s why we’re using the ‘scope’ argument.
Are you logging in as sysdba?
Are you including the ‘scope = spfile’ on the alter system commands?
Wow! this info saves me. Thank you so much Sir Andrew for this guide…it really helps me a lot…
Thanks a lot !
The step are simple to understand and helped me a lot.
Good Work !!
Thank You very much, Andrew..! U r a savior.. : )
Wah, keren banget mas. Infonya bagus banget! Makasih banyak!
Thanks a lot!
This article is really helpful for me
Thanks a lot!
It works!!!
Hi Andrew
Thanks for your valuable tips…. I am facing another problem about the max size of the express database limit (4 GB). Would you please help me in this regard ? Is it possible to increase the database size of oracle express database ?
Hi Sutanu,
The 4GB data limit is a fixed limit for Oracle Express, and cannot be changed.
Your upgrade path is to Oracle Standard Edition One, which has a minimum 5 user license and costs GBP £737.70 (about USD $1,170). Definitely call Oracle first, because they have new company discounts.
Thank you very much it works fine but
my database has not started yet?
what is the reason Andew.
Thanks @Yalkoki
Do you have any error messages, or does it look like it’s just sitting there thinking about it?
It should come back within a couple of minutes. If it doesn’t, try restarting the Oracle services
Thank you Andrew
After restarting the service it works.
Thank you Andrew
really awesome description keep it …!!!!!!!!!!!
Thanks a Lot !! Andrew
It worked for me as well.
Awesome, explanation. It worked for me. Thanks.
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS
PROCESSES = 150
SESSIONS = 300
TRANSACTIONS = 330
PROCESSES = 150
SESSIONS = (1.1 * 150) +5 = 170
TRANSACTIONS = (1.1 * 170) = 187
I got a bit confused with these values, please help. I have set the values as 150, 300 and 330 but need a bit more clarification for the calculation.
Hi Sunil,
You’re correct, starting with a PROCESSES value of 150, and following the Oracle documentation, you’d get
PROCESSES = 150
SESSIONS = (1.1 * 150) + 5 = 170
TRANSACTIONS = (1.1 * 170) = 187
But I wanted Oracle XE to handle more than 170 SESSIONS, so I could have started with the number of SESSIONS I wanted (I think of SESSIONS as concurrent connections), and worked out the other two values like so:
SESSIONS = x
PROCESSES = (SESSIONS – 5) / 1.1
TRANSACTIONS = (1.1 * SESSIONS)
which would have given me
SESSIONS = 300
PROCESSES = ((300 – 5) / 1.1) = 268
TRANSACTIONS = (1.1 * 300) = 330
Where did my 300 SESSIONS come from? At the time I wrote this article, I was supporting an internal Oracle XE installation that had ~80 users, so I trebled it to cover those power admin users that run multiple copies of the application (which gives me 240), then rounded it up to 300 because I like round numbers – though 256 is arguably the closest round number..
Hi,
I am new to Oracle. Am running Oracle Database 10g Enterprise Edition Release 10.2.0.1.0. Am in Financial Institution.
I have met settings for processes, sessions and transactions being 150,170 and 187 respectively.
After deploying one application that passes through data to Oracle, the application log has been registering ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded.
I want to increase the above parameters values. Can you help me with Guidance?
Hi Leonard,
Sure, what guidance do you need?
If the values are 150, 170 and 178 and you’re still seeing ORA-00604, increase your SESSIONS, PROCESSES and TRANSACTIONS again, to a higher number.
What number you ask? well how about 2x or 3x the number of users as a guide for SESSIONS, then working out PROCESSES and TRANSACTIONS as described in my last comment above?
Hope that helps
Dear Andrew,
Please could you help me?
I’ve change parameter processes to value 258 and sessions to 516 but Oracle change sessions automatically to 520. Could you tell me why? And is it problem?
Thank you for your reply
Hi Stefan, thanks for your comment. The short answer is I don’t know why Oracle sets the SESSIONS as 520 when you specify 516. If your Oracle Database is starting up, then no, it’s not a problem!
It is great to see this kind of crystal clear article, Thank you so much. this helped a lot.