Find and delete blocking sessions- Oracle

Following query would give you all the session running in your oracle database with query details

select sesion.sid,
sesion.serial#,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

Now you might want to kill an unwanted session

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

Leave a Reply