About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, February 25, 2013

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

7 comments:

  1. Thanks.. it was very much helpful as I am a newbie as a Oracle Admin

    ReplyDelete
  2. Thanks mate. It helped me !!

    Ashok

    ReplyDelete
  3. Thanks for share helpful information!!!

    ReplyDelete
  4. Thank you for the Tips. very helpful.

    ReplyDelete
  5. Nice presentation. Very helpful.

    ReplyDelete
  6. Thanks, that's was very helpful.

    ReplyDelete
  7. i want to kill those INACTIVE SESSION in which no query is running...for those can we make an script???

    ReplyDelete