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     
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) )
  dbms_output.put_line('kill -9 '||r.spid);
    execute immediate;
    when others then
end loop;
dbms_output.put_line('If you want to kill the server processes too, run previous output in shell');

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


and more …


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: