How to generate script to kill multiple Oracle sessions

Killing Oracle session :-

TO kill a oracle session you have to be very sure which session you want o kill otherwise you may kill any other session which is useful to you.

To 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 where username='USERNAME' order by logon_time;

Alter system kill session 'sid,serial#' immediate;

Disconnecting Oracle sessions :-

Disconnecting a session is similar to kill a session . Unlike Kill session asks session to kill itself, disconnect session kill the dedicated server process equivalent to killing from OS level.
Syntax wise disconnect has a additional clause called POST_TRANSACTION, it waits for ongoing transactions to complete before disconnecting the session while IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immeiately.

Alter system disconnect session 'sid,serial#' POST_TRANSACTION;
Alter system disconnect session 'sid,serial#' IMMEDIATE;

When in our database we have multiple inactive sessions and we want to kill all of them, then we can generate a script to kill all of them.

Small Script to kill multiple oracle sessions where status is INVALID:-

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

Small Script to kill multiple oracle sessions of a particular user :-

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

Finding how much a session is executed

SQL>col OPNAME for a20
SQL>col USERNAME for a18
SQL> col START_TIME for a25
SQL> select sid,serial#,opname,sofar,totalwork,username,to_char(start_time,'dd-mon-yyyy hh24:mi:ss') as "START_TIME",time_remaining from v$session_longops where username='SYSTEM' and time_remaining!=0;

I hope this article helped you.

Regards,
Amit Rath