I'm trying to connect as the master user or DBA user on Amazon Relational Database Service (Amazon RDS) for Oracle, but I receive one of the following errors:

ORA-00018 maximum sessions exceeded

Or

ORA-00020 maximum processes exceeded

These errors can be caused by either a planned scaling exercise or an unplanned event that causes a large number of DB connections. In these cases, many client sessions are initiated against a DB instance, and one of the following database limits is reached:

  • PROCESSES – the maximum number of user processes allowed.
  • SESSIONS – the maximum number of user sessions allowed.

If the maximum connections are reached because of a planned scaling exercise, you might need to increase SESSIONS or PROCESSES, or both, to accommodate your application’s new scale. These two parameters are not dynamic, so modify the parameters and then reboot the instance.

If the maximum connections are reached because of an unplanned event, identify the cause of the event and take appropriate action. For example, your application might overwhelm the database when response times increase because of locking or block contention. In this case, increasing SESSIONS or PROCESSES only increases the number of connections before maxing out again, which can worsen issues caused by contention.

Note: Oracle no longer uses LICENSE_MAX_SESSIONS. For more information, see Oracle Documentation 1.132 LICENSE_SESSIONS_WARNING.

Set LICENSE_MAX_SESSIONS to a number less than the limit defined by PROCESSES or SESSIONS. LICENSE_MAX_SESSIONS applies to client connections only, not to background processes or to users who have RESTRICTED SESSION privileges, such as the master user. By setting changing the LICENSE_MAX_SESSIONS limit, you force the clients to receive an ORA-00019 “Maximum number of licenses exceeded” error instead of ORA-18 or ORA-20. Because an ORA-00019 error doesn’t apply to restricted users, your master user can connect to the instance and reboot the instance. This resolves the error by killing unnecessary connections or releasing lock holders.

LICENSE_MAX_SESSIONS is a dynamic parameter, so it can be set without restarting the RDS instance. For more information, see Working with DB Parameter Groups. See the following steps based on whether you use SHARED or DEDICATED sessions:

DEDICATED sessions

If you use DEDICATED sessions, your client connections might exceed the limit of the PROCESSES parameter (ORA-20). If your client connections exceed the limit, set the value of the LICENSE_MAX_SESSIONS below PROCESSES as follows:

  • LICENSE_MAX_SESSIONS = maximum number of client connections only.
  • PROCESSES = LICENSE_MAX_SESSIONS + all background processes, including parallel queries, DBA users including master users, and a buffer. A buffer allows for unexpected background processes that might occur later. To see how many background processes you have now, run a query similar to the following:
SQL> select count(*) from v$session where type= 'BACKGROUND';

Note: SESSIONS, which defaults to (1.5 * PROCESSES) + 22, should be sufficient. For more information, see SESSIONS.

To manually connect to your instance to verify the SESSIONS, run a command similar to the following:  

SQL> select name, value from v$parameter where upper(name) in ('SESSIONS','PROCESSES','LICENSE_MAX_SESSIONS');
NAME   VALUE
------------------------------ ------------------------------
processes   84
sessions   148
license_max_sessions   0

SHARED sessions

If you use SHARED sessions, your client connections might exceed the limit of the SESSIONS parameter (ORA-0018). If your client connections exceed the limit, set the PROCESSES parameter to a higher value.

  • LICENSE_MAX_SESSIONS = maximum number of client connections only.
  • PROCESSES = all background processes, including parallel queries, and DBA users including master users, and a buffer. Be sure to include settings of SHARED_SERVERS and DISPATCHERS with the count of background processes.
  • SESSIONS = (1.5 * PROCESSES) + 22

If you use SHARED servers, and you receive a max processes (ORA-20) error instead of max sessions (ORA-18) error, it’s likely that your dispatchers are overwhelmed. When dispatchers are overwhelmed, the connections are forced to come in as DEDICATED. In this case, increase the number of DISPATCHERS to allow more sessions to connect shared. The SHARED_SERVERS parameter might also need to be increased. Consult Oracle documentation for more information.

If you don't know whether you're using SHARED or DEDICATED servers, you can verify which server you are using by running a query similar to the following:

SQL> select decode(server, 'NONE', 'SHARED', server) as SERVER, count(*)
from v$session group by decode(server, 'NONE', 'SHARED',server)

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2016-06-24

Updated: 2018-10-22