Alex Rivera | Logout

How can I kill all sessions connecting to my oracle database?

Asked 2008-09-10T21:16:10.613
30

I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.

I don't want to just lock the database and let the users quit gracefully.

How would I script this?

Edit
Report

1 Answer

5

I've been using something like this for a while to kill my sessions on a shared server. The first line of the 'where' can be removed to kill all non 'sys' sessions:

BEGIN
  FOR c IN (
      SELECT s.sid, s.serial#
      FROM v$session s
      WHERE (s.Osuser = 'MyUser' or s.MACHINE = 'MyNtDomain\MyMachineName')
      AND s.USERNAME <> 'SYS'
      AND s.STATUS <> 'KILLED'
  )
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || '''';
  END LOOP;
END;
answered 2011-10-18T10:55:32.377

Your Answer