In this article, I will review different situations and methods to manage Oracle Database sessions.

Kill 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
	;