Find long running queries with v$session longops with percentage of completion

Long running queries are find with help of v$session_longops view. It will captured the SID, elapsed time of long running queries. We can join it with other view to fetch more information. So that, it will help us in seeing how long the process is running, estimate how long it will run more in Oracle

Find the SQL text for 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 ='216' and serial# = '31254'
order by start_time desc)
where rownum <=1;

Note:
Sofar Column: Work already done
Elapsed Seconds Column: already spent seconds for work

Check the time remaining for the process with session longops view

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;


Find the SID from the PID in Oracle database

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;


Get Username,status and serial# on bases of SID from session view

select sid,serial#,USERNAME,status from v$session where sid = 1226;


Get active session details

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 ;