Query to find long running queries in oracle database

 

Use below query to find the sessions which are running from long time.

set lines 200 pages 200
cle bre
col sid form 99999
col start_time head "Start|Time" form a12 trunc
col opname head "Operation" form a12 trunc
col target head "Object" form a40 trunc
col totalwork head "Total|Work" form 9999999999 trunc
col Sofar head "Sofar" form 9999999999 trunc
col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc
col tre head "Time|Remain|(Min)" form 999999999 trunc
select sid,sql_id,to_char(start_time,'dd-mon:hh24:mi') start_time,
         opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
         (time_remaining/60) tre
from v$session_longops
where totalwork <> SOFAR
order by tre, start_time,sid
/

Thanks for going through the post.............