How-to kill oracle sessions as you wish

If you want to kill a kind of sessions quickly, using this:

set serveroutput on size unlimited     
begin
dbms_output.put_line('Killing the session...');
for r in (select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate' st, p.spid
 from v$session s, v$process p
 where s.paddr=p.addr and (&where_stmt) )
loop
  dbms_output.put_line('kill -9 '||r.spid);
  begin
    execute immediate r.st;
  EXCEPTION
    when others then
     null;  
  end;
end loop;
dbms_output.put_line('If you want to kill the server processes too, run previous output in shell');
end;
/

For example:
1. Kill sessions idled more than 1 hour:

status = ‘INACTIVE’ and LAST_CALL_ET > 3600

2. Kill sessions wait on “library cache: mutex X”:

event = ‘library cache: mutex X’

3. Kill sessions from user TEST_APP:

username like ‘TEST_APP’

4. Kill sessions running a giving SQL

sql_hash_value=499912407

and more …

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: