How to Check the Maximum Number of Allowed Connections to an Oracle Database

How to check the maximum number of allowed connections to an Oracle database?

There are a few different limits that might come in to play in determining the number of connections an Oracle database supports. The simplest approach would be to use the SESSIONS parameter and V$SESSION, i.e.

The number of sessions the database was configured to allow

SELECT name, value 
FROM v$parameter
WHERE name = 'sessions'

The number of sessions currently active

SELECT COUNT(*)
FROM v$session

As I said, though, there are other potential limits both at the database level and at the operating system level and depending on whether shared server has been configured. If shared server is ignored, you may well hit the limit of the PROCESSES parameter before you hit the limit of the SESSIONS parameter. And you may hit operating system limits because each session requires a certain amount of RAM.

How to know the number of database connections

Bit confused with your statement I'm not asking about the number of sessions, but connections.

Conceptually both are same. Every active session will correspond to a underlying active connection to the database.

Now, if you meant to know the max allowed connection limit then Documentation says

Maximum number of connections (system and application) across all
databases in an instance = 2048

To know the allowed session configured to your database, you can query v$parameter view like

SELECT name, value 
FROM v$parameter
WHERE name = 'sessions'

If you want to know the Active session at any instance Out of total configured to allow then you can query v$session view using the Status column like

SELECT COUNT(*)
FROM v$session
WHERE STATUS = 'ACTIVE'

You may want to refer this post How to check the maximum number of allowed connections to an Oracle database?

How to find the maximum number of active sessions reached in the last 24 hours in Oracle?

Found the answer. The required information can be found by querying the DBA_HIGH_WATER_MARK_STATISTICS table.

The required NAME is SESSIONS which gives us the Maximum Number of Concurrent Sessions seen in the database.

Check here for more details.

Is there a maximum number of connections allowed on Oracle9i DB?


Is there some kind of maximun number of connections allowed on the databas

Yes.

SESSIONS is one of the basic initialization parameters and

specifies the maximum number of sessions that can be created in the
system. Because every login requires a session, this parameter
effectively determines the maximum number of concurrent users in the
system.

The default value is derived from the PROCESSES parameter (1.5 times this plus 22); therefore if you didn't change the PROCESSES parameter (default 100) the maximum number of sessions to your database will be 172.

You can determine the value by querying V$PARAMETER:

SQL> select value
2 from v$parameter
3 where name = 'sessions';

VALUE
--------------------------------

480

so when it surpass that limit the database queues my query for sometime before trying again?

No.

When you attempt to exceed the value of the SESSIONS parameter the exception ORA-00018: maximum number of sessions exceeded will be raised.

Something may well be queuing your query but it will be within your own code and not specified by Oracle.


It sounds as though you should find out more information. If not at the maximum number of sessions then you need to capture the query that's taking a long time and profile it; this would, I think, be the more likely scenario. If you're at the maximum number of sessions then you need to look at your (companies) code to determine what's happening.

You haven't really explained anything about your application but it sounds as though you're opening a session (or more) per user. You might want to reconsider whether this is the correct approach.

Oracle - Maximum number of sessions

I am not sure about the maximum limit on process bucket. Though I can share some information on this. If you exceed this limit, you will face an exception called ORA-00020 maximum number of processes (somevalue) exceeded.

This means you cannot connect or any process cannot establish a connection due to either zombies or low number of buckets allocated. You can increase the values of process bucket. This will allow more processes to be connected. But there are some derived parameters which will be impacted. These derived parameters include sessions, enqueue_resources, and _enqueue_hash_chains. Do read about them and decide.

Note: I never suggest you to pass on some high value to find the error code with the allowed value



Related Topics



Leave a reply



Submit