Identify Oracle Long Running sessions

1) The following query will identify any session which are running for more than 10 seconds.


select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_running,
q.sql_text
from
v$session s join v$sqltext_with_newlines q on s.sql_address = q.address
where
status='ACTIVE'
and
type <>'BACKGROUND'
and
last_call_et> 10 --time in seconds
order by
sid,
serial#,
q.piece;





2) The following query will identify any session which are running for more than 10 seconds and the SQL statement to kill that session.


select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_running,
q.sql_text,
'Alter system kill session '''||s.sid||','||serial#||''' immediate;' as SQL_to_kill_long_running_sqls
from
v$session s join v$sqltext_with_newlines q on s.sql_address = q.address
where
status='ACTIVE'
and
type <>'BACKGROUND'
and
last_call_et> 10 --time in seconds
order by
sid,
serial#,
q.piece;