Note to Self: Killing Oracle Sessions

February 18, 2009 at 10:26 pm (Note to Self, Oracle)

To find all sessions for a specific user connected via the Oracle JDBC driver:

select * from v$session where username='X' and program like '%JDBC%';

To kill all of these database sessions:

declare
  statement varchar(100);
  cursor c is
    select 'alter system kill session '''
      || sid || ',' || serial# || ''' immediate'
    from v$session
    where username='X'
    and program like '%JDBC%';
begin
  dbms_output.enable;
  open c;
  loop
    fetch c into statement;
    exit when c%notfound;
    dbms_output.put_line('Executing: ' || statement);
    execute immediate statement;
  end loop;
  close c;
end;

This will result in output similar to the following:

Executing: alter system kill session '225,46' immediate
Executing: alter system kill session '226,110' immediate
Executing: alter system kill session '233,6617' immediate

1 Comment