Oracle database for long running queries
alter session set nls_date_format = ‘DD-MM-YYYY HH24:MI:SS’;
SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round(l.time_remaining/60,2) “REMAIN MINS”, round(l.elapsed_seconds/60,2) “ELAPSED MINS”, round((l.time_remaining+l.elapsed_seconds)/60,2) “TOTAL MINS”, ROUND(l.SOFAR/l.TOTALWORK*100,2) “%_COMPLETE”, l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE ‘RMAN%’ AND l.OPNAME NOT LIKE ‘%aggregate%’ AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0
/
