SID SERIAL# STATUS MACHINE OSUSER LOGON_TIMEġ 1 ACTIVE CentralEtickDB oracle 2 03:55:15Ģ 1 ACTIVE CentralEtickDB oracle 2 03:55:16ħ 26205 INACTIVE production-two tomcat 1 11:46:10ġ1 30356 INACTIVE nfc-UAT nfc 1 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 :- select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate ' from v$session where username='MIR' 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE 'Īlter system kill session '3,52181' immediate Īlter system kill session '4,33082' immediate Īlter system kill session '5,26612' immediate Īlter system kill session '6,20138' immediate Īlter 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 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' 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 ġ3 rows selected. Note: You can’t kill the active Session therefore the session should be INACTIVE to perform the kill operation as shown below alter system kill session '456,211' immediate Īlter system kill session '781,7775' immediate

Immediate clause disconnects the session and ongoing transactions are rolled back immediately.

Post_Transaction clause actually it waits for ongoing transactions to complete before disconnecting the session 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. Or To kill the session: alter system kill session '167,38231’ alter system disconnect session '167,38231' immediate Testdb 11.2.0.4.0 OPEN Fetch the session with respective USER select sid, serial#,status from v$session where username = 'MIR' ġ67 38231 INACTIVE To disconnect the session: alter system disconnect session '167,38231' post_transaction Database Status: select instance_name,version,status from v$instance

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.

Oracle kill session