longops_mon.sql

select  s.inst_id,
        SQL.SQL_TEXT as "OPERATION",
        to_char(START_TIME, 'dd/mm/yyyy hh24:mi:ss') Start_Time,
        to_char(LAST_UPDATE_TIME, 'dd/mm/yyyy hh24:mi:ss') Last_Update_Time,
        round(TIME_REMAINING/60,1) as "MINUTES_REMAINING",
        round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE
from    gv$session s, 
        gv$sqlarea sql, 
        gv$session_longops op
where     
        s.sid=op.sid
and     s.sql_id = sql.sql_id
and     s.sid = op.sid
and     s.status  = 'ACTIVE'
and     op.totalwork > op.sofar
and     s.sid not in (select distinct sid from gv$mystat where rownum < 2)
order by 4 desc;