Solutions to ORA-02391 Problems

The Analysis and Solution of the ORA problem is actually a good learning idea. Grasp every ORA error and further analyze some causes. In conclusion, there will always be different gains.

The Analysis and Solution of the ORA problem is actually a good learning idea. Grasp every ORA error and further analyze some causes. In conclusion, there will always be different gains.

The Analysis and Solution of the ORA problem is actually a good learning idea. Grasp every ORA error and further analyze some causes. In conclusion, there will always be different gains, there is a reason behind any problem.

This morning, a developer reported that a ORA error was thrown on the client.

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

I hope we can help you to see why and how to fix it.

This problem is quite clear, that is, the corresponding session number limit will be defined in the profile we set. For example, if the user test and sessions_per_user are set to 50, the user test can use up to 50 sessions.

The DEFAULT profile is DEFAULT, and basic Initialization is performed after the database is created, such as the password expiration time.

SQL> select * from DBA_PROFILES WHERE PROFILE = 'default'
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
In the DEFAULT profile DEFAULT, sessions_per_user is Unlimited. However, to restrict resource usage, we still need to customize the profile. In fact, it is still based on profile DEFAULT.
SQL> select username, profile from dba_users where username = 'user_test ';
USERNAME PROFILE
------------------------------------------------------------
USER_TEST APP_TEST
At this time, we can see that the maximum number of sessions is 20.
SQL> select * from dba_profiles where profile = 'app _ test ';
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------------------------
APP_TEST COMPOSITE_LIMIT KERNEL DEFAULT
APP_TEST SESSIONS_PER_USER KERNEL 20
APP_TEST CPU_PER_SESSION KERNEL DEFAULT
APP_TEST CPU_PER_CALL KERNEL DEFAULT
APP_TEST LOGICAL_READS_PER_SESSION KERNEL DEFAULT
APP_TEST LOGICAL_READS_PER_CALL KERNEL DEFAULT
APP_TEST IDLE_TIME KERNEL DEFAULT
APP_TEST CONNECT_TIME KERNEL DEFAULT
APP_TEST PRIVATE_SGA KERNEL DEFAULT
APP_TEST FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
APP_TEST PASSWORD_LIFE_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_REUSE_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_REUSE_MAX PASSWORD DEFAULT
APP_TEST PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
APP_TEST PASSWORD_LOCK_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_GRACE_TIME PASSWORD DEFAULT
The basic knowledge has been introduced.