col opname format a20

col sofar format 99999999

col totalwork format 9999999999999

col target format a20

col target_desc format a10

col username format a10

col units format a10

col time_remaining format a10 head 'TIME|REMAIN|MINUTES'

col start_time format a22 head 'START TIME'

col message format a25

select

        sid

, sql_id

, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') current_time

,(sofar/totalwork)*100 PCT

                , substr('00'||to_char(trunc(time_remaining/60)),-2,2)

                || ':'

                || substr('00'||to_char(mod(time_remaining,60)),-2,2)

                time_remaining

        , username

        , opname

        , sofar

        , totalwork

, units

        , to_char(start_time,'YYYY/MM/DD HH24:MI:SS') start_time

from v$session_longops

where time_remaining > 0

order by username

SELECT osuser,

           sl.sql_id,

           sl.sql_hash_value,

           opname,

           target,

           elapsed_seconds,

           time_remaining

  FROM v$session_longops sl

inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#

WHERE time_remaining > 0

OR

SELECT s.username,

           sl.sid,

           sq.executions,

           sl.last_update_time,

           sl.sql_id,

           sl.sql_hash_value,

           opname,

           target,

           elapsed_seconds,

           time_remaining,

           sq.sql_fulltext

  FROM v$session_longops sl

 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id

 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#

 WHERE time_remaining > 0