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.