CASS

---longops.sql listing of v$SESSION_LONGOPS
set pages 1000 lines 160
col SID format 9999
col USERNAME format a12
col SERIAL# format 999999
col OSUSER format a12
col PROGRAM format a30
col MACHINE format a20
col LOGON_TIME format a15
col command format  999
col CMD format  999 

col LAST_UPDATE format a12 

col OPNAME format a20
col TARGET format a30
col UNITS format a12
col SOFAR format 999999
col REMAIN format 999999
col ELAPS format 999999
col HASH_VALUE format 9999999999 

set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance;
set termout on serveroutput on heading on 

TTitle Left '###  v$SESSION_LONGOPS Details ordered by LAST_UPDATE_TIME  -  ' title_name skip 2 

select SID,OPNAME,TARGET,SQL_HASH_VALUE HASH_VALUE, SOFAR,TOTALWORK,UNITS,START_TIME,LAST_UPDATE_TIME,TIME_REMAINING remain,ELAPSED_SECONDS ELAPs
from v$SESSION_LONGOPS
order by LAST_UPDATE_TIME; 

TTitle Left '###  v$SESSION_LONGOPS summary ordered by HASH_VALUE  -  ' title_name  skip 2 

select SQL_HASH_VALUE HASH_VALUE, to_char(LAST_UPDATE_TIME,'DD-MON-RR') LAST_UPDATE, OPNAME,TARGET,
min(ELAPSED_SECONDS) MIN_ELAPS, max(ELAPSED_SECONDS) MAX_ELAPS, round(avg(ELAPSED_SECONDS))AVG_ELAPS, count(*)
from v$SESSION_LONGOPS
group by SQL_HASH_VALUE, to_char(LAST_UPDATE_TIME,'DD-MON-RR'),OPNAME,TARGET
order by LAST_UPDATE,HASH_VALUE; 

TTitle off