Oracle long running SQL ... 

long running SQL 
long running SQL queries

set pages 300 lines 300
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#, 
''''||sid ||','||serial#||',@'||inst_id ||'''' kill,
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 A50
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
col kill for a17
SELECT 
--s.sid, s.serial#, 
''''||s.sid ||','||s.serial#||',@'||s.inst_id ||'''' kill,
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# AND TOTALWORK != 0;






set linesize 500 pagesize 500
col MESSAGE for a50  
col "USERNAME| SID,SERIAL#,inst" for a40
col "STARTED|MIN_ELAPSED|REMAIN" for a25
 select USERNAME||'| '||''''||sid ||','|| serial#||',@'||inst_id ||'''' "USERNAME| SID,SERIAL#,inst",SQL_ID,round(SOFAR/TOTALWORK*100,2) "%DONE"
        ,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) "STARTED|MIN_ELAPSED|REMAIN" ,SQL_ID,MESSAGE
        from gv$session_longops
    where SOFAR/TOTALWORK*100 <>'100'
	     and TOTALWORK <> '0'
		-- and MESSAGE not like 'RMAN:%'
        order by "STARTED|MIN_ELAPSED|REMAIN" desc, "USERNAME| SID,SERIAL#,inst";






set lines 500
col opname format a35
col target format a25
col units format a10
col kill for a17
col message for a20
col kill for a17
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
      order by start_time desc)
/