Mir Sayeed Hassan – Oracle Blog

How to Kill Single & Multiple Sessions in Oracle Database 11gR2

To kill the Oracle session in a database, you should be careful with the consult team to kill the correct session as per there requirement.

Database Status:

sys@TESTDB> select instance_name,version,status from v$instance;

INSTANCE_NAME    VERSION            STATUS
 ---------------------------------------------------------
 testdb          11.2.0.4.0          OPEN

Fetch the session with respective USER

sys@TESTDB> select sid, serial#,status from v$session where username = 'MIR';

SID    SERIAL#      STATUS
----------------------------
167    38231       INACTIVE

To disconnect the session:

sys@TESTDB> alter system disconnect session '167,38231' post_transaction;
System altered.

sys@TESTDB> alter system disconnect session '167,38231' immediate;
System altered.

Or

To kill the session:

sys@TESTDB> alter system kill session '167,38231’;
System altered.

Brief Note: The disconnecting a session is very much similar to “KILL” a session, whereas “KILL” session inform session to kill itself but the disconnect session it kill the dedicated server process which is equivalent to killing from OS level.

Post_Transaction clause actually it waits for ongoing transactions to complete before disconnecting the session
Immediate clause disconnects the session and ongoing transactions are rolled back immediately.

Note: You can’t kill the active Session; therefore the session should be INACTIVE to perform the kill operation as shown below

sys@TESTDB> alter system kill session '456,211' immediate;
 alter system kill session '781,7775' immediate
 *
 ERROR at line 1:
 ORA-00027: cannot kill current session

Find the ACTIVE/INACTIVE with respective to users in database

SQL> select s.status, count(1), s.username from v$process p, v$session s where paddr(+)=addr group by s.status, s.username order by 1;

STATUS COUNT(1) USERNAME
--------------------------
ACTIVE     1     SYS
ACTIVE     1     SYSMAN
INACTIVE   4     DBSNMP
INACTIVE   9     CHARGES
INACTIVE   1     PUBLIC
INACTIVE  797    REALNFC
INACTIVE   7     SYSMAN
INACTIVE   1     SYSTEM

13 rows selected.

If you would like to kill the multiple INACTIVE session at a same time, Perform the below query

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
 -------------------------------------------------------------
 alter system kill session '3,52181' immediate;
 alter system kill session '4,33082' immediate;
 alter system kill session '5,26612' immediate;
 alter system kill session '6,20138' immediate;
 alter system kill session '7,25950' immediate;

Find your session and machine from which you are connected

SQL> select sid,serial#,status,machine,osuser,to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME from v$session;

SID     SERIAL#     STATUS      MACHINE             OSUSER      LOGON_TIME
 ---------- ---------- -------- ---------------------------------------------------
 1         1        ACTIVE     CentralEtickDB       oracle     29-OCT-2018 03:55:15
 2         1        ACTIVE     CentralEtickDB       oracle     29-OCT-2018 03:55:16
 6       20145     INACTIVE    nfc-UAT              nfc        15-JAN-2019 11:08:04
 7       26205     INACTIVE    production-two       tomcat     15-JAN-2019 11:46:10
 11      30356     INACTIVE    nfc-UAT              nfc        15-JAN-2019 11:42:59

Find the INACTIVE session which are using last more than 24 hours

SQL> select s.last_call_et,s.username, s.* FROM v$session s WHERE s.last_call_et > (60*60*24) and status = 'INACTIVE' order by 1 desc;

If you wish you KILL multiple sessions of a particular user :-

sys@TESTDB> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='MIR';
no rows selected

We can use the profile to kill the session as per our requirement

Create a Profile as shown below:

There is different way to control the session to use in database profile, 2 types are

CONNECT_TIME: The maximum elapsed time in minutes for a session, whether it is active or not.
IDLE_TIME: The maximum period of continuous inactive time in minutes.

Therefore the session which exceed the resource limits are killed by using the profile to a user.

If you wish to kill sessions which is older than 2 hr

sys@TESTDB> create profile KILL_PROFILE_2HR limit connect_time 120;
Profile created.

Kill sessions that have been inactive for 1 hour.

sys@TESTDB> create profile KILL_INACTIVE_PROFILE_1HR limit idle_time 60;
Profile created.

Kill sessions older than 2 hours or inactive for 1 hour.

sys@TESTDB> create profile KILL_OLD_OR_INACTIVE_PROFILE limit connect_time 120 idle_time 60;
Profile created.

Assign the relevant profile to a user: Example: USER – “MIR”

sys@TESTDB> alter user MIR profile KILL_OLD_OR_INACTIVE_PROFILE;
User altered.

sys@TESTDB> select username,account_status,profile from dba_users where username='MIR';

USERNAME       ACCOUNT_STATUS                    PROFILE
-----------------------------------------------------------------
  MIR                 OPEN          KILL_OLD_OR_INACTIVE_PROFILE

sys@TESTDB> SELECT SYSTIMESTAMP(3) FROM dual;

SYSTIMESTAMP(3)
 ---------------------------------------------------------------------------
15-JAN-19 02.24.41.933 PM +03:30

sys@TESTDB> select sid,serial#,status from V$session where username='MIR';

SID  SERIAL#  STATUS
-----------------------
173  16827   INACTIVE

As per the applied profile to the user “MIR”, it’s should get killed with the given condition with respect to given profile.

sys@TESTDB> select sid,serial#,status from V$session where username='MIR';
no rows selected.

Hence tested & verified in our test env.