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