Find long running query and time of query how long it running

Check long running queries :


col sql_text format a100
set linesize 400

SELECT l.sid, l.start_time, l.username, l.elapsed_seconds
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea
WHERE a.elapsed = l. elapsed_seconds
AND l.elapsed_seconds > 1
/


Check long running process for particular session  and sid :

select * from (

  select opname, target, sofar, totalwork,

         units, elapsed_seconds, message

  from v$session_longops

  where sid = and serial# =

  order by start_time desc)

where rownum <=1;

 work already done (column SOFAR)

 already spent seconds for work (column ELAPSED_SECONDS) 

Check the time remaining :

SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;

Identify database SID based on OS Process ID

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

select sid,serial#,USERNAME,status from v$session where sid in (1226);



SELECT USERname,terminal,SID,SERIAL#,SQL_TEXT,V$session.module
FROM V$SESSION, V$SQL
WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
AND V$SESSION.STATUS = 'ACTIVE'
AND SQL_TEXT not like '%USERname%' AND username IS NOT NULL and sid = 1226 ;