-- 1. Basic query

SELECT * FROM v$session_longops 
WHERE sofar != totalwork;

-- 2. Detail query

SELECT
 s.sid, s.serial#  serial_No,
 s.username, s.program, s.module,
 lo.opname  operation_,
 TO_CHAR(lo.start_time,'mm/dd/yyyy hh24:mi:ss') start_time,
 TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
 lo.sofar||'/'||lo.totalwork  work_ratio,
 ROUND(100*lo.sofar/lo.totalwork,5) pct_complete,
 lo.target, lo.elapsed_seconds,
 lo.time_remaining,
 lo.units,
 q.sql_text  exec_query,
 s.status,  lo.message
FROM  v$session_longops lo,
      v$session s,
      v$sql q
WHERE lo.sid = s.sid
AND   lo.serial# = s.serial#
AND   s.sql_address = q.address
AND   lo.sofar != lo.totalwork;


SELECT
 s.sid, s.serial#  serial_No,
 s.username, s.program, s.module,
 lo.opname  operation_,
 TO_CHAR(lo.start_time,'mm/dd/yyyy hh24:mi:ss') start_time,
 TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
 lo.sofar||'/'||lo.totalwork  work_ratio,
 ROUND(100*lo.sofar/lo.totalwork,5) pct_complete,
 lo.target, lo.elapsed_seconds,
 lo.time_remaining,
 lo.units,
 q.sql_text  exec_query,
 s.status,  lo.message
FROM  v$session_longops lo,
      v$session s,
      v$sql q
WHERE lo.sid = s.sid
AND   lo.serial# = s.serial#
AND   s.sql_address = q.address
AND   lo.time_remaining > 0;


SELECT
  sl.sid, sl.serial#, sl.sofar, sl.totalwork,
  ROUND(100*sl.sofar/sl.totalwork,5) pct_complete,
  dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl,
     v$datapump_job dp
WHERE  sl.opname = dp.job_name
AND    sl.sofar != sl.totalwork
;


-- PL/SQL code below shows setup to create data in the v$session_longops, 
-- as Oracle completes processing each of the ten steps. Any of the above 
-- queries (1 or 2) can be used to analyze the long executing processes.

DECLARE
  v_rindex     PLS_INTEGER;
  v_slno       PLS_INTEGER;
  v_sofar      PLS_INTEGER := 0;
  v_totalwork  PLS_INTEGER := 10;

BEGIN
 v_rindex := Dbms_Application_Info.Set_Session_Longops_Nohint;

 WHILE sofar <= v_totalwork LOOP 
   v_sofar := v_sofar + 1;
   DBMS_APPLICATION_INFO.set_session_longops(rindex => v_rindex, 
                                             slno => v_slno,
                                             op_name => 'MView Refresh',
                                             target => v_obj, 
                                             context => 1000,  
                                             sofar => v_sofar, 
                                             totalwork => v_totalwork,  
                                             target_desc => 'EDW_FACT_TABLE',  
                                             units => 'Rows Processed');
 END LOOP;

END;
/