-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Details about long operations 
-- Call Syntax  : @long_ops_info.sql 
-- Last Modified: 17/03/2015
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN SID FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT a25
COLUMN progress_pct FORMAT 99999999.00
COLUMN remaining_mi FORMAT a10
COLUMN elapsed_mi FORMAT a10
COLUMN username FORMAT a20
COLUMN opname FORMAT a20
COLUMN module FORMAT a20
COLUMN PROGRAM FORMAT a45
COLUMN osuser FORMAT a20
SELECT S.SID,
  S.serial#,
  S.machine,
  S.username,
  S.status,
  S.osuser,
  S.PROGRAM,
  S.sql_id,
  sl.opname,
  round(sl.elapsed_seconds/60)
  || ':'
  || MOD(sl.elapsed_seconds,60) elapsed_mi,
  round(sl.time_remaining/60)
  || ':'
  || MOD(sl.time_remaining,60) remaining_mi,
  round(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session S,
  v$session_longops sl
WHERE S.SID   = sl.SID AND status='ACTIVE'
AND S.serial# = sl.serial#;