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)
;
