In this article, I will review different situations and methods to manage Oracle Database sessions.
When I wish to kill all the session running a specific SQL and I know the SQL Id we can use the statement below which will create a list of kill session statements that need to be run separately
select
'alter system kill session '''||
sid||
', '||
serial#||
',@'||
inst_id||
''' immediate;'
from gv$session
where sql_id = '38vzjncs5p883'
;
When I wish to kill all the sessions started by a specific module, we can use the statement below which will create a list of kill session statements that need to be run separately
select
'alter system kill session '''||
sid||
','||
serial#||
',@'||
inst_id||
''';/*'||
sql_id||
'*/' "Session Kill Command"
from gv$session
where module = 'module-name-here'
and sql_id is not null
order by sql_id
;