Limit user sessions in Oracle by USERNAME Wise

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.

There is one Oracle Initialization Parameter        

RESOURCE_LIMIT

Property Description
Parameter type Boolean
Default value false
Modifiable ALTER SYSTEM
Range of values true | false

 

RESOURCE_LIMIT:- which determines whether resource limits are enforced in database profiles.

 Step-1 Configure the RESOURCE_LIMIT is true

 SQL> show parameter RESOURCE_LIMIT

 NAME                                 TYPE        VALUE

————————————————————-

resource_limit                       boolean     FALSE

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=true scope=BOTH;

System altered.

SQL> show parameter RESOURCE_LIMIT

NAME                                 TYPE        VALUE

——————————————————————-

resource_limit                       boolean     TRUE

 Step-2 Create the Profile SESSLIMIT

 SQL> CREATE PROFILE MYSESSLIMIT LIMIT SESSIONS_PER_USER 2;

 Profile created.

 

Step-3 Assign the Created Profile to Scott User

 SQL> ALTER USER SCOTT  PROFILE MYSESSLIMIT;

User altered.

 

Step-4 Testing Scenario:-

 

Terminal-A Session-1

C:\>sqlplus scott/tiger@mydb:1521/orcl

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Nov 24 11:51:40 2012

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

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

SQL>

Terminal B Session-2

C:\>sqlplus scott/tiger@mydb:1521/orcl

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Nov 24 11:51:40 2012

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

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

SQL>

Terminal C Session-3


C:>sqlplus scott/tiger@mydb:1521/orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 24 11:51:16 2012

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Enter user-name:

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s