SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS 
FROM gV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=b.sid 
--AND a.sid=&sid
--AND b.status='ACTIVE' 
AND a.sofar<> a.totalwork;
-- ---------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' 
AND OPNAME NOT LIKE '%aggregate%' 
AND TOTALWORK != 0 
AND sofar<>totalwork 
AND time_remaining > 0;
-- ---------------------------------------------------------------------------------------------------
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   gv$session s,
       gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;
-- ---------------------------------------------------------------------------------------------------
SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS, 
    TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME, 
    TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME, 
    TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME 
    FROM V$SESSION_LONGOPS 
WHERE TIME_REMAINING != 0;