Oracle kill all sessions from one schema.

Sometimes you need to kill all sessions from one schema. You can do that with following sql's.

1.) Kill all session from one schema from single instance:


SQL> set head off
SQL> spool kill_sessions.tmp
SQL> select 'alter system kill session '''||sid||','||serial#|| ''' immediate ;' from v$session where username='TEST_USER' ;
SQL> spool off
SQL> @kill_sessions.tmp


When you run kill_sessions.tmp script it will give you two "SP2-0734: unknown command beginning ..."
errors because there are orginal select clause and spool off  in file. But you can ignore those. Or you can also remove those two lines from tmp file before running it.


2.) Kill all sessions from one schema from whole RAC cluster (all instances):

SQL> set head off
SQL> spool kill_sessions_rac.tmp
SQL> select 'alter system kill session '''||sid||','||serial#|| ',@' || inst_id || ''' immediate;' from gv$session where username='TEST_USER' ;
SQL> spool off
SQL> @kill_sessions_rac.tmp

When you run kill_sessions.tmp script it will give same errors as single instance version.

And of course you can also run only select clause and run manually kill clauses it prints.