I got ” ORA-02391 Exceeded simultaneous session_per_user limit ” error in Oracle database.
ORA-02391 Exceeded simultaneous session_per_user limit
Details of error are as follows.
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit Cause: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile. Action: End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.
exceeded simultaneous SESSIONS_PER_USER limit
This ORA-02391 errors are related with the attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.
The owner of the job is assigned to unlimited SESSIONS_PER_USER profile:
SQL> select profile from dba_users where username = 'USER1'; PROFILE ------------------------------ APPLICATION_USER SQL> select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from dba_profiles where PROFILE = 'APPLICATION_USER' PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT -------------------- ------------------------ --------------- --------------- APPLICATION_USER COMPOSITE_LIMIT KERNEL DEFAULT APPLICATION_USER SESSIONS_PER_USER KERNEL UNLIMITED
End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.
1. Assign SYS a profile with sufficient SESSIONS_PER_USER, e.g. unlimited
OR
2. Increase SESSIONS_PER_USER for the profile assigned to SYS, until errors are no longer reported
Check your user limit as follows.
select PROFILE, LIMIT from dba_profiles WHERE RESOURCE_NAME = 'SESSIONS_PER_USER' AND PROFILE = 'PROFILE_NAME';
SESSIONS_PER_USER Parameter
Then you can limit the profile’s limit as follows.
alter profile PROFILE_NAME limit SESSIONS_PER_USER 150;
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )