ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit - ORA-02063: preceding line from

This is happening because the User/Schema has made the maximum number of connections allowed. This is governed by the parameter SESSIONS_PER_USER in the user profile. Once the maximum number of connections are made you will hit this error.

You can get the details by executing the following queries :

*****

*****

select profile from dba_users where username ='USER1';

PROFILE

--------------------

DEFAULT

*****

*****

select * from dba_profiles where profile='DEFAULT';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------

DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

DEFAULT                        SESSIONS_PER_USER                KERNEL   50

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 10

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

Say for example in the above scenario User1 is allowed to make 50 connections only to the database.

We can check how many connections have been made to the database using the following query.

*****

*****

select count(*) from v$session where username='USER1';

 COUNT(*)

---------

        34

The resolution is to change the parameter SESSIONS_PER_USER in the profile attached to user or Identifying if the application is hitting the bug , if so many connections to database is not anticipated.

The profile can be altered using :

Alter profile DEFAULT  limit sessions per user 100;