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 10.2.0.1.0 - 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 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
Connected.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- I'll start two sessions in another terminal.
SQL> connect fish/fish123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


SQL>

4 comments:

Oracle Pl/SQL said...

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

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

Anonymous 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...

Hi,
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.