–Ref: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1198012113596
column dur format a20
set lines 120 pages 200
select a.sid, a.serial#, a.username,
to_char(sysdate-a.last_call_et/24/60/60,’hh24:mi:ss’) started,
trunc(a.last_call_et/60) || ‘ mins, ‘ || mod(a.last_call_et,60) ||’ secs’ dur,
b.sql_text
from v$sql b, v$session a
where a.username is not null
and a.last_call_et > 10
and a.status = ‘ACTIVE’
and a.sql_address = b.address;