ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

There are many ways to overcome this issue

Method 1 - Increase parameter

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check current configuration of profile

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' 
and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- -------------------
APP01     DEFAULT             SESSIONS_PER_USER                500

3. Increase parameter value

SQL> ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 600;

Profile altered.

4. Makesure parameter has been changed successfully

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- --------------------
APP01     DEFAULT             SESSIONS_PER_USER                600

Method 2 - Kill Idle sessions based on duration

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Select Idle connection which are inactive more than 12 hours

set heading off;
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where username='APP01' and status = 'INACTIVE' and last_call_et/60/60 >= 12;

3. Kill Idle sessions from above output

alter system kill session '100,35175' immediate;
alter system kill session '1768,53954' immediate;
alter system kill session '2129,15490' immediate;
alter system kill session '2251,6956' immediate;
alter system kill session '3331,17248' immediate;
...............

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01

Method 3 - Kill sessions which create connection from particular machine/program

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check hosts which make multiple sessions

select machine,program,username,count(*)
from v$session 
group by machine,program,username
order by 4 desc;

machine  program  username  count(*)
-------  -------  --------  --------
e5a07b31a6e5	JDBC Thin Client	APP01	470
demo3021132.isaaviation.net	JDBC Thin Client	APP02	185

3. Kill from above output

begin
for s in(
    select *
    from v$session
    where machine = 'e5a07b31a6e5' and username = 'APP01'
)
loop
    begin
    execute immediate 'alter system kill session '''||s.sid||','||s.serial#||''' immediate';
    exception when others then
        null;
    end;
end loop;
end;

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01