Information on “RESOURCE_LIMIT” parameter

“RESOURCE_LIMIT” parameter is used to enable/disable “KERNEL” resource type profiles. Password parameters are part of profiles but they are controlled by this parameter. This parameter is a SYSTEM level parameter so it can be changed using “ALTER SYSTEM” and can be changed on the fly. Possible values are true/false and the default value is false.

Example: To set resource_limit
sys@TESTDB> alter system set resource_limit = true scope=both;

System altered.

Example: Creates a profile and sets one_session limit to 1 and assigned the profile to scott
scott@TESTDB> create profile one_session limit sessions_per_user 1;

Profile created.

scott@TESTDB> alter user scott profile one_session;

User altered.

scott@TESTDB> connect scott/tiger
Connected

In another window
C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 2 22:00:50 2011

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Displays the resource limits for the current user
scott@TESTDB> select * FROM USER_RESOURCE_LIMITS;

RESOURCE_NAME LIMIT
——————————– ————————-
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER 1
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

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