Monday, July 30

Limit user sessions in Oracle

Developers sometimes enjoy configuring their JDBC datasources after their own likings, testing a performance issue by having a gazzillion threads to the database "just in case we need them".
Well in my opinion, if you need what is obviously too many threads, then there is a coding issue.
So to make people think twice before flooding a service with connections I've started limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration;
oracle@htpc:~$ rsqlplus "/ as sysdba"

SQL*Plus: Release - Production on Mon Jul 30 21:56:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 2;

Profile created.

SQL> create user fish identified by fish123
2 default tablespace data profile sesslimit;

User created.

SQL> grant create session to fish;

Grant succeeded.

SQL> connect fish/fish123
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- I'll start two sessions in another terminal.
SQL> connect fish/fish123
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit



Oracle Pl/SQL said...

hi sir,
can you please tell me what is the maximum number of sessions allowed in oracle 10g database.

Unknown said...

Good post.

Anonymous said...

Christian Louboutin
As a way to design and style remarkably one of a kind and effective Christian Louboutin Petal Sandals; they consider benefit of your very last equipment and technologies in order that you’ll be fully capable of get the brightest and designer sandal designs from them in way. Seems wise, the Christian Louboutin Petal Sandals are extremely inimitable and flexible shoes in an attempt to seize your attentions directly. On the subject of the colours, the monumental fascination is that they use superlative colors for making fancy your Christian Louboutin Petal Sandals.

mahakk01 said...

I am not one of the supporters to limit user session in Oracle. I like the way you have describe the topic. I think user should be given maximum number of sessions in Oracle. The coding for the same is easy to perform. Thanks for the information in this post.
sap erp system

Unknown said...

I am on of those developers that does not enjoy configuring JDBC datasources, it is a time consuming task, what I like is to take Generic Viagra and configure a romantic setting for my wife and I

Oracle Insights said...

is there any way that we can customize the error message rather displaying ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit ?

Eg: We can write any client event trigger and display the custom message ?
Pls suggest.