long running SQL   ..

set linesize 800 pagesize 300
col perc 				format 9999999999
col sql_exec_start 			format a22
col username 				format a12
col opname 				format a30
col target 				format a40
col message 				format a80
col parallel_coordinator 	        format 9999999999
col fetches 				format 9999999999
col executions 				format 9999999999
col loads 				format 9999999999
col sql_fulltext 			format a70 wrap
col kill for a16
col progress_pct format 99999999.00
col elapsed format a10
col remaining format a10
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,
       decode(sl.totalwork, 0, 0, round((sl.sofar * 100) / sl.totalwork, 2)) as perc,
       to_char(sql_exec_start, 'dd.mm.yyyy hh24:mi:ss') as sql_exec_start,
       sl.username,
       sl.opname,
       sl.target,
       sl.message,
       qcsid as parallel_coordinator,
       s.fetches,
       s.executions,
	   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,
       s.loads,
	   s.sql_id,
       dbms_lob.substr(lob_loc => s.SQL_FULLTEXT, amount => 4000) AS SQL_FULLTEXT
from   gv$session_longops sl ,  gv$sql s 
where 1=1
and s.address = sl.sql_address
and s.hash_value = sl.sql_hash_value
and s.inst_id = sl.inst_id
-- and  sl.start_time > sysdate - &num_days.
 --and decode(sl.totalwork, 0, 0, round((sl.sofar * 100) / sl.totalwork, 2)) !=100
 -- AND sl.opname not LIKE 'RMAN%' 
 -- AND sl.opname NOT LIKE '%aggregate%'
order  by perc, username,sql_exec_start;



 set linesize 300 pagesize 1000
col	 instance_name 			FORMAT a10              HEADING 'Instance'
col	 sid 							HEADING 'Oracle|SID'
col	 serial_num 						HEADING 'Serial|#'
--COLUMN opname 			FORMAT a30 	        HEADING 'RMAN|Operation'
col	start_time 			FORMAT a18 	        HEADING 'Start|Time'
col	totalwork 				   	        HEADING 'Total|Work'
col	sofar 							HEADING 'So|Far'
col	pct_done 						HEADING 'Percent|Done'
col	elapsed_seconds 					HEADING 'Elapsed|Seconds'
col	time_remaining 						HEADING 'Seconds|Remaining'
col	done_at format a18 					HEADING 'Done|At'
col kill for a15
col event for a30
col program for a20
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
,a.sql_id
,PREV_SQL_ID
, b.opname opname
, TO_CHAR(b.start_time, 'dd/mm/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork, 0, 0.001, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
,a.event
, DECODE( b.time_remaining, 0, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'dd/mm/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'dd/mm/yy HH24:MI:SS')) done_at
,PROGRAM
FROM gv$session a, gv$session_longops b
where 1=1
and a.sid=b.sid
and a.serial#=b.serial#
and a.inst_id=b.inst_id
-- and a.program LIKE 'rman%' AND b.opname LIKE 'RMAN%' AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY  b.start_time;
  



set lines 500
col opname format a35
col target format a10
col units format a10
col kill  for a15
col message for a50 wrap
select * from (
      select
      ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, sql_id,
      opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min  , sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id,  TO_CHAR(sql_exec_start, 'DD-MM-YYYY HH24:MI:SS') sql_exec_start  ,message
      from gv$session_longops
      WHERE sofar < totalwork
    -- and sql_id='g75x2hpgkgqcg'  <<<< for this sql 
      order by start_time desc)
/

set lines 500
col kill   for a18
col OPNAME for a35
col "%complete" for 999999.99
select 
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,OPNAME, (sofar/totalwork)*100 as "%complete", ELAPSED_SECONDS, TIME_REMAINING
from gv$session_longops
where 1=1 
and sofar<>totalwork
and totalwork<>0
and SID||SERIAL# in (select SID||SERIAL# from gv$session)
;