drop table ash_gtt;
create global temporary table ash_gtt (
          sample_id       number,
          sample_time     date,
          sid             number,
          state           varchar2(20),
          serial#         number,
          user#           number,
          sql_address#    varchar2(20),
          sql_hash        number,
          command         number,
          session_type    number,
          event#          number,
          seq#            number,
          p1              number,
          p2              number,
          p3              number,
          wait_time       number,
          row_wait_obj#   number,
          row_wait_file#  number,
          row_wait_block# number,
          program         varchar2(64),
          module_hash     number,
          action_hash     number
);


CREATE OR REPLACE PACKAGE ash AS
          TYPE ash_ref_cur IS REF CURSOR RETURN ash_gtt%ROWTYPE;
          PROCEDURE print (sleep number, loops number) ;
          PROCEDURE collect (sleep number, loops number) ;
          FUNCTION get (sleep number, loops number) RETURN ash_ref_cur;
          FUNCTION get_dbid  return number ;
          END ash;
/

CREATE OR REPLACE PACKAGE BODY ash AS

       FUNCTION get_dbid return number is
          db_id number;
          begin
            select dbid into db_id from v$database;
            return db_id;
            -- return 1;
       end get_dbid;


       PROCEDURE print(sleep number, loops number) is
          ash_cur ash_ref_cur;
          ash_rec ash_cur%rowtype;
          begin
            ash_cur:=ash.get(sleep,loops);
            loop
                fetch ash_cur into ash_rec;
                exit when ash_cur%notfound;
                dbms_output.put_line(ash_rec.sample_time||' '||
                                     to_char(ash_rec.sid)||' '||
                                     ash_rec.state);
            end loop;
            close ash_cur;
       end print;

       PROCEDURE collect(sleep number, loops number) is
          ash_cur ash_ref_cur;
          ash_rec ash_cur%rowtype;
          db_id number;
          begin
            db_id:=get_dbid;
            ash_cur:=ash.get(sleep,loops);
            loop
                fetch ash_cur into ash_rec;
             /*
                dbms_output.put_line(ash_rec.sample_time||' '||
                                     to_char(ash_rec.sid)||' '||
                                     ash_rec.state);
            */

               insert into ash_data@REPO values (
                             db_id,
                             ash_rec.sample_id      ,
                             ash_rec.sample_time   ,
                             ash_rec.sid          ,

                            ash_rec.state       ,
                             ash_rec.serial#    ,
                             ash_rec.user#     ,
                             ash_rec.sql_address# ,
                             ash_rec.sql_hash    ,
                             ash_rec.command     ,
                             ash_rec.session_type    ,
                             ash_rec.event#          ,
                             ash_rec.seq#            ,
                             ash_rec.p1              ,
                             ash_rec.p2              ,
                             ash_rec.p3              ,
                             ash_rec.wait_time       ,
                             ash_rec.row_wait_obj#   ,
                             ash_rec.row_wait_file#  ,
                             ash_rec.row_wait_block# ,
                             ash_rec.program         ,
                             ash_rec.module_hash     ,
                             ash_rec.action_hash
                      );
                exit when ash_cur%notfound;

            end loop;
            close ash_cur;
       end collect;

       FUNCTION get(sleep number, loops number) RETURN ash_ref_cur IS
           ash_cur ash_ref_cur;
           ash_rec ash_cur%rowtype;
          begin
            for i in 1..loops loop
             insert into ash_gtt
              Select
                 to_char(sysdate,'SSSSS')+trunc(sysdate-to_date('JAN-01-1970 00:00:00','MON-DD-YYYY HH24:MI:SS'))*86400 ,
                 sysdate,
                 s.indx          "SID",
                 decode(w.ksusstim,
                            0,decode(n.kslednam,
                              'db file sequential read', 'I/O',

                              'db file scattered read','I/O',
                              'WAITING'),
                        'CPU') "STATE",
                 s.ksuseser      "SERIAL#",
                 s.ksuudlui      "USER#",
                 s.ksusesql      "SQL_ADDRESS",
                 s.ksusesqh      "SQL_HASH_VALUE" ,
                 s.ksuudoct      "COMMAND"  /* aka SQL_OPCODE */,
                 s.ksuseflg      "SESSION_TYPE"  ,
                 w.ksussopc      "EVENT# ",
                 w.ksussseq      "SEQ#"        /* xksuse.ksuseseq */,
                 w.ksussp1       "P1"          /* xksuse.ksusep1  */,
                 w.ksussp2       "P2"          /* xksuse.ksusep2  */,
                 w.ksussp3       "P3"          /* xksuse.ksusep3  */,
                 w.ksusstim      "WAIT_TIME"   /* xksuse.ksusetim */,
                 s.ksuseobj      "ROW_WAIT_OBJ#",
                 s.ksusefil      "ROW_WAIT_FILE#",
                 s.ksuseblk      "ROW_WAIT_BLOCK#",
                 s.ksusepnm      "PROGRAM",
                 s.ksuseaph      "MODULE_HASH",  /* ASH collects string */
                 s.ksuseach      "ACTION_HASH"   /* ASH collects string */
       from
               x$ksuse s ,
               x$ksusecst w,
               x$ksled n
       where
               s.indx != ( select distinct sid from v$mystat ) and
               bitand(s.ksspaflg,1)!=0 and
               bitand(s.ksuseflg,1)!=0 and
               n.indx=w.ksussopc and
               s.indx = w.indx and
            (  (
                  /* status Active - seems inactive & "on cpu"=> not on CPU */
                  w.ksusstim != 0  and  /* on CPU  */
                  bitand(s.ksuseidl,11)=1  /* ACTIVE */
               )
                     or
               w.ksussopc not in   /* waiting and the wait event is not idle */
                   ( select
                             event#
                    from
                             v$event_name
                     where
                            lower(name) in (
                                 'queue monitor wait',
                                 'null event',
                                 'pl/sql lock timer',
                                 'px deq: execution msg',
                                 'px deq: table q normal',
                                 'px idle wait',
                                 'sql*net message from client',
                                 'sql*net message from dblink',
                                 'dispatcher timer',
                                 'lock manager wait for remote message',
                                 'pipe get',
                                 'pmon timer',
                                 'queue messages',
                                 'rdbms ipc message',
                                 'slave wait',
                                 'smon timer',
                                 'virtual circuit status',
                                 'wakeup time manager',
                                 'i/o slave wait',
                              'jobq slave wait',
                                 'queue monitor wait',
                                 'SQL*Net message from client'
                            )
                       )
              );
              dbms_lock.sleep(sleep);
            end loop;
            open ash_cur for select * from ash_gtt;
            return ash_cur;
       END get;
END ash;
/