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
SQL Between Clause with Strings Columns
Why Does a Like Query in Access Not Return Any Records
Scope of Temporary Tables in SQL Server
Conversion of a Varchar Data Type to a Datetime Data Type Resulted in an Out-Of-Range Value
How to Delete Duplicates in MySQL Table
How to Avoid Duplicate Values for Insert in SQL
"Operator Does Not Exist: Integer =" When Using Postgres
Record Returned from Function Has Columns Concatenated
SQL to Find the Number of Distinct Values in a Column
How to Query for All Dates Greater Than a Certain Date in SQL Server
What Datatype to Use When Storing Latitude and Longitude Data in SQL Databases
How to Check the Maximum Number of Allowed Connections to an Oracle Database
How to Show All Privileges from a User in Oracle
When How to Save JSON or Xml Data in an SQL Table
How to Sum Two Fields Within an SQL Query