set serveroutput on;
--
-- set your session identifications here
--
define sid=123;
define inst=1;
exec dbms_output.enable(500000);
DECLARE
BEGIN
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line('************* Start report for WAITING sessions with current SQL ***************');
FOR x
IN (SELECT vs.inst_id,
vs.sid || ',' || vs.serial# sidser,
vs.sql_address,
vs.sql_hash_value,
vs.last_call_et,
vsw.seconds_in_wait,
vsw.event,
vsw.state
FROM gv$session_wait vsw, gv$session vs
WHERE vsw.sid = vs.sid
AND vsw.inst_id = vs.inst_id
AND vs.TYPE <> 'BACKGROUND'
AND vs.sid = &sid2093
AND vs.inst_id =&inst
AND vsw.event NOT IN
('rdbms ipc message',
'smon timer',
'pmon timer',
'SQL*Net message from client',
'lock manager wait for remote message',
'ges remote message',
'gcs remote message',
'gcs for action',
'client message',
'pipe get',
'Null event',
'PX Idle Wait',
'single-task message',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status',
'slave wait',
'wakeup time manager'))
LOOP
BEGIN
DBMS_OUTPUT.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
DBMS_OUTPUT.put_line('************************* ******************** ****** *********** ********** *************');
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line( RPAD (x.event, 45)
|| ' '
|| RPAD (x.state, 20)
|| ' '
|| LPAD (x.inst_id, 6)
|| ' '
|| LPAD (x.sidser, 11)
|| '
'
|| LPAD (x.last_call_et, 10)
|| ' '
|| LPAD (x.seconds_in_wait, 13));
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line('****************************************************************');
DBMS_OUTPUT.put_line (' SQLText ');
DBMS_OUTPUT.put_line('****************************************************************');
FOR y
IN (SELECT sql_text
FROM gv$sqltext
WHERE address = x.sql_address
AND hash_value = x.sql_hash_value
AND inst_id = x.inst_id
ORDER BY piece)
LOOP
DBMS_OUTPUT.put_line (y.sql_text);
END LOOP;
END;
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line('****************************************************************');
DBMS_OUTPUT.put_line (' Progress ');
DBMS_OUTPUT.put_line ('Operation | Percent | Target|Start Time | Message| Expected End');
DBMS_OUTPUT.put_line('****************************************************************');
DBMS_OUTPUT.put_line (' ');
BEGIN
FOR y
IN (SELECT sid,
serial#,
opname,
target,
target_desc,
sofar,
totalwork,
MESSAGE,
TO_CHAR (CASE
WHEN totalwork = 0 THEN 1
ELSE sofar / totalwork
END
* 100,
'990'
)
percent,
start_time,
last_update_time,
start_time + ( (elapsed_seconds + time_remaining) / 86400)
efin
FROM gv$session_longops
WHERE sid || ',' || serial# = x.sidser AND inst_id = x.inst_id
ORDER BY CASE WHEN sofar = totalwork THEN 1 ELSE 0 END,
efin DESC)
LOOP
DBMS_OUTPUT.put_line( y.opname
|| ' | '
|| y.percent
|| ' | '
|| y.target_desc
|| ' | '
|| TO_CHAR (y.start_time,
'dd.mm.yyyy hh24:mi:ss'
)
|| '|'
|| y.MESSAGE
|| ' | '
|| TO_CHAR (y.efin, 'dd.mm.yyyy hh24:mi:ss'));
END LOOP;
END;
END LOOP;
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line('************** Ende : report for sessions ****************');
DBMS_OUTPUT.put_line (' ');
END;
/