Query to check long running query status in oracle

 

Sometime SQL queries takes long time to complete. 

if we want to monitor Progress we can check with use of  v$session_longops script which will show you, % completed, remaining time, sofar completed 

and much more detailed information .

From below query you can check the result.

SELECT
s.sid,s.username,
sl.opname,
sl.target,
ROUND((sl.sofar/sl.totalwork),4)*100 Percentage_Complete,
sl.start_time,
CEIL(sl.time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(sl.elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops sl, v$session s
where sl.sid=s.sid
and sofar != totalwork;