How Long SQL will take

select *
from ( select
opname
,target
,round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) time_remaining
,sofar
,totalwork
,units
,elapsed_seconds
,message
,a.start_time
,b.sql_text
from v$sql b
,v$session_longops a
where b.sql_id (+) = a.sql_id
and round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) > 0
)
order by time_remaining desc ;



CREATE OR REPLACE FUNCTION sys.get_sqltext(p_hash_value NUMBER)
RETURN VARCHAR2 IS
l_sql_text VARCHAR2(32767) := '';
l_sql_left NUMBER := 4000;
BEGIN
FOR i IN(SELECT * FROM sys.v$sqltext
WHERE hash_value = p_hash_value ORDER BY piece
) LOOP
IF l_sql_left > 64 THEN
l_sql_text := l_sql_text || i.sql_text;
ELSIF l_sql_left > 0 THEN
l_sql_text := l_sql_text || SUBSTR(i.sql_text,1,l_sql_left);
END IF;
l_sql_left := l_sql_left - LENGTH(i.sql_text);
END LOOP;
RETURN l_sql_text;
END get_sqltext;
/

show errors
GRANT EXECUTE ON sys.get_sqltext TO PUBLIC;

spool longops
SELECT l.*, NVL(s.sql_text
, sys.get_sqltext(l.sql_hash_value)) sql_text
FROM (
SELECT l.target, l.operation, l.sql_hash_value
, SUM(secs) secs, SUM(execs) execs
FROM (
SELECT l.sid, l.serial#, l.sql_address, l.sql_hash_value
, l.target, l.operation
, MAX(l.last_update_time-l.start_time)*86400 secs
, COUNT(*) execs
, SUM(totalwork) totalwork
FROM (
SELECT l.*
, SUBSTR(l.message,1,instr(l.message,':',1,1)-1) operation
FROM v$session_longops l) l
GROUP BY l.sid, l.serial#, l.sql_address
, l.sql_hash_value, l.target, l.operation
) l
GROUP BY l.target, l.operation, l.sql_hash_value
) 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
ORDER BY secs desc
/
spool off

===========

COL how_long FORMAT 99,990 HEAD “Time|Run”
COL secs_left FORMAT 99,990 HEAD “Appr.|Secs Left”
COL sofar FORMAT 9,999,990 HEAD “Work|Done”
COL totalwork FORMAT 9,999,990 HEAD “Total|Work”
COL percent FORMAT 999.90 HEAD “%|Done”
–
select
a.username
,a.opname
,b.sql_text
,to_char(a.start_time,’DD-MON-YY HH24:MI’) start_time
,a.elapsed_seconds how_long
,a.time_remaining secs_left
,a.sofar
,a.totalwork
,round(a.sofar/a.totalwork*100,2) percent
from v$session_longops a
,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sofar <> a.totalwork
and a.totalwork != 0;