
begin
 FOR x
         IN (SELECT vs.inst_id,vs.username, vs.machine,
                    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 = v_sid
                    AND vs.inst_id = vsw.inst_id
                    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.username, 45)
               || ' '
                || RPAD (x.machine, 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;