Oracle 11g Maximum Processes/Sessions/Transactions limits
What I'm doing
I'm working on an Oracle driver incorporating OCI. To test it (mostly stability and fixing memory leaks), I'm using Oracle Database 11g Express Edition (I'm not an Oracle expert, so I don't know if this is the right version for the job). For this, it needs to commit a lot of transactions at once: dozens of connections simultaneously, each one maintaining a session pool where dozens of sessions each are simultaneously creating tables, inserting lots of data and dropping the tables.
Anyway, I quickly got the following error doing that:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
As it turns out, that's because the Processes/Sessions/Transaction limits aren't high enough. Searching on the Internet, I was told that they can be set this way:
alter system set processes=1000 scope=spfile;
alter system set sessions=1000 scope=spfile;
alter system set transactions=1000 scope=spfile;
Apparently, the truth is more complicated than that because those values get set depending on each other, and how that works seems to be different in each version. According to Oracle's documentation here it all depends on Processes:
The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter.
And Processes can NOT be changed:
Modifiable No
...while at the same time it is talking about what happens when you change it:
Therefore, if you change the value of PROCESSES [...]
So that's not very helpful. Or perhaps I'm misunderstanding something here. Anyway, using the alter system calls above I changed the values, and the changes are successful. When I make this call
(select 'sessions', current_utilization, limit_value from v$resource_limit where resource_name='sessions')
union
(select 'processes', current_utilization, limit_value from v$resource_limit where resource_name='processes')
union
(select 'transactions', current_utilization, limit_value from v$resource_limit where resource_name='transactions');
I get the following result:
'SESSIONS' CURRENT_UTILIZATION LIMIT_VALUE
------------ ------------------- ----------------------------------------
processes 314 1000
sessions 317 1524
transactions 2 UNLIMITED
It looks like processes could be changed unlike what Oracle's documentation says, sessions got set to 2 more than the processes * 1.5 + 22 rule described here, and transactions seems to be unlimited. The change is effective, I can now make many more connections and sessions without encountering Error ORA-12516. So far, so good.
My Problem
I wanted even more connections, so I tried setting it to 10000. However, when I did, the database wouldn't even start properly. When I try to connect to it using sqlplus, I get the following message:
Connected to an idle instance
I can't use the database in any way, and startup won't work either. I reinstalled the database, set it to 1000, reboot and that worked. Reinstalling and rebooting without changing a setting also works. Reinstalling it, setting it to 10000 and then rebooting, however, doesn't work. This leads me to believe that there's a limit to how high those values can be set and that exceeding the limit will render the database unable to start properly.
So my question is: what are the maximum Processes/Sessions/Transactions limits that I can set for the Oracle Database 11g Express Edition? Are there any factors that influence such a limit that I could control/change? Or am I horribly misunderstanding all of this and the solution is something entirely different? The machine I'm running this on is a fairly powerful machine running Windows 10, so power shouldn't be an issue. Except maybe if the software is enforcing an artificial limit, and I did find such comments on my search.
Thanks in advance.