In Oracle, to set a session limit per schema (user) – i.e., control how many concurrent sessions a specific user can have. You use profiles with the SESSIONS_PER_USER parameter.
Step-by-Step Guide to Set Session Limit per Schema.
1. Create a Profile with a Session Limit
If the profile doesn’t exist already, create one :
SQL > CREATE PROFILE limited_sessions_profile LIMIT SESSIONS_PER_USER 40;
This limits users assigned to this profile to 40 concurrent sessions.
2. Assign the Profile to a User (Schema)
SQL > ALTER USER target_user PROFILE limited_sessions_profile;
Replace target_user with the username (schema) you want to restrict.
3. Check the Profile Assignment and Limit
To verify the profile assigned to the user :
SQL > SELECT username, profile FROM dba_users WHERE username = ‘TARGET_USER’;
4. To check session limits in the profile:
SQL > SELECT * FROM dba_profiles WHERE profile = ‘LIMITED_SESSIONS_PROFILE’ AND resource_name = ‘SESSIONS_PER_USER’;
5. Optional: Modify an Existing Profile
If the user already has a profile, and you want to add a session limit to it:
SQL > ALTER PROFILE existing_profile LIMIT SESSIONS_PER_USER 3;
Important Notes :
This setting does not immediately terminate sessions if they’re over the limit. It prevents new logins once the session count limit is reached. You can check current session counts with :
SQL > SELECT username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username;