How to List all Active Sessions in Oracle and Kill it

 

The following SQL lists all Active Sessions.  It is useful to locate performance problem.  It shows every running SQL statement prefix.  You can also get the kill statement, just copy and run it to kill the problem session.

with vs as (select a.sid, a.serial#, a.status, a.username, a.last_call_et, a.command, a.machine, a.osuser, a.module, a.action, a.resource_consumer_group, a.client_info, a.client_identifier, a.type, a.terminal,                         a.sql_address, a.sql_hash_value, b.sqltext                      from gv$session a left join (select address, hash_value, listagg(trim(sql_text),'') within group (order by piece) sqltext                                                             from V$SQLtext_with_newlines                                                             where piece < 20                                                             group by address,hash_value) b on a.sql_address = b.address and a.sql_hash_value = b.hash_value )   select vs.sid ,vs.serial# serial, --vs.sql_id,         vs.username,         case when vs.status = 'ACTIVE'                    then last_call_et               else null end SECONDS_IN_WAIT,         (select name from AUDIT_ACTIONS where action = vs.command) COMMAND,         vs.machine ,         vs.osuser ,          lower(vs.status) STATUS,         vs.module,         vs.action,         vs.resource_consumer_group,         vs.client_info,         vs.client_identifier,         vs.sqltext sqlprefix,         'ALTER SYSTEM KILL SESSION '''||vs.sid||','||vs.serial#||''' immediate;' kill_statement    from vs    where vs.USERNAME is not null     and nvl(vs.osuser,'x') <> 'SYSTEM'     and vs.type <> 'BACKGROUND'     and lower(vs.status) not in ('inactive') ;

The following list some useful columns as an example.