Script to kill session for particular schema

Script to Kill session at Oracle Database : Sometime we see huge number of session connected to the database and application folks wants us to kill all those session. Below are scripts to drop all the session connected to a schema and second script is to drop all the inactive sessions connected to schema.


Use below command to see the number of session connected, there status and many more. You can check v$session for more option.

select username,sid,serial#, status,machine,osuser from v$session where username='ASHISH';

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To drop all the sessions connected to a schema:

select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where username='ASHISH';

You can spool the output and run the entire script in one go:

vi create_drop_all_user_script.sql

spool /tmp/drop_user.sql

set pages 10000
set lines 1000
select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where username='ASHISH';
spool off

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Similarly, to drop all inactive session for a particular schema:

select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where status='INACTIVE' AND username='ASHISH';


You can spool the output and run the entire script in one go:

vi create_drop_inactive_user_script.sql

spool /tmp/drop_inactive_user.sql

set pages 10000
set lines 1000
select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where status='INACTIVE' AND username='ASHISH';
spool off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++