Oracle Cloud: ORA-00018 maximum number of sessions exceeded -> Use DRCP
One of my customers want to try out the Oracle Cloud, and they started with a small database using the “Always Free” option.
So far, so good, except one issue – from time to time, they are getting the error message “ORA-00018: maximum number of sessions exceeded“.
Every Oracle DBA would first check the database init parameter “sessions” and increase it, if it’s set too low. You can display it so:
SQL> show parameter sessions NAME TYPE VALUE ------------- -------------- ---------- sessions integer 20
or so:
SQL> SELECT name, value
FROM v$parameter
WHERE name = 'sessions'
NAME VALUE
--------------- -----------
sessions 20
And when you try to increase it, you’ll get an error message:
SQL> ALTER SESSION SET SESSIONS=50;
Error report -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without the necessary privileges.
If you’re asking yourself “Why am I not allowed to that?! I am logged in as an ADMIN user!!“, don’t panic. It is a known restriction for “Always Free Autonomouns Database” as stated here: Resource Restrictions for Always Free Autonomous Database (a big thanks to Hemant K. Chitale for pointing it out!).
- Maximum of 20 simultaneous database sessions
- Always Free Autonomous Databases cannot be scaled manually or automatically beyond the fixed resource restrictions described above.
- The Maximum of 20 simultaneous database sessions limit for Always Free and 1 OCPU per database allows you to work with Autonomous Databases; however, if your usage includes many simultaneous users and/or many concurrent database client connections then you can exceed these limits, resulting in errors. To avoid such errors, obtain more resources for your Autonomous Database by upgrading to paid service.
Well, the customer will later upgrade to paid service, but for now they want just to test the base functionality of the application. Just a couple of minutes before I had to write a status update to the customer, I got the great tip from Franck Pachot, one of the best Oracle Experts in the world, who wrote me, that DRCP (Database Resident Connection Pool) can be used in this case.
According to the Oracle Documentation, it should really help me to solve this issue:
“Database Resident Connection Pool (DRCP) in Autonomous Database supports easier and more efficient management of open connections. Using DRCP provides you with access to a connection pool in your Autonomous Data Warehouse database that enables a significant reduction in key database resources required to support many client connections and when the database needs to scale for many simultaneous connections.“
The solution is to add this parameter SERVER=POOLED to the connection string, for example:
customersdb_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=sdadadadyfkglzp_customersdb_low.atp.oraclecloud.com)(SERVER=POOLED)
)(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")(MY_WALLET_DIRECTORY=D:\app\Oracle_DB_Client_19.3\network\admin\CUSTOMERSDB)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)))
Problem solved. The Oracle Community is great as always and it helped me a lot of times, to solve some tricky issues.
For efficient use of DRCP, it’s important for apps to set the DRCP connection class, and to use an Oracle connection pool. There are various docs on this, e.g for Python cx_Oracle see https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp
Hi Christopher,
thanks for the info – that’s a good point! Unfortunately, this feature (DRCP) is not so much used by the customers and I will try to raise awareness of it.