# local schema to install package, should be SYS
  SCHEMA=sys
  PW="sys as sysdba"
  TARG_HOST=`hostname`
  TARG_SID=$ORACLE_SID
  TARG_HOME=$ORACLE_HOME

# REPOSITORY - need schema/passsword and HOST and SID
  REPO_SCHEMA=ash
  REPO_PW=ash
  REPO_SID=cdb
  REPO_HOST=cont01
  REPO_PORT=1521

# number of partitions, based on day of week Sun =1 Sat=7
  nparts=7

#  HOW TO USE:
#  set serveroutput on
#  execute dbms_output.enable(1000000);
#  -- print data 10 times at 1/10 sec intervals
#  exec  ash.print(.1,10); 
#  -- collect data 30  times at 1 sec intervals
#  exec  ash.print(1,30);
# 
#  -- collect data into v$ash every sec for an hour
#  -- Oracle job runs this every hour
#  variable job number
#  exec dbms_job.submit(:job,'ash.collect(1,3600);',sysdate,'trunc(sysdate+(1/(24)),''HH'')');
#  -- need the commit to actually submit the job
#  commit;
#
#  -- remove jon
#  -- exec dbms_job.remove(:job);  
#  
#  -- look at data
#  select sample_time, session_id, session_state from v$ash;
#  -- count samples taken
#  select count(*) from v$ash;
#

x=1
# trick , 0 - just create TABLES, 1 - just create package, 2 - do both
TABS=${1:-2}
CREATE=ashcr.sql
DROP=ashdr.sql
DBL="@REPO"
DBLINK="ashdbl.sql"

if [  $TABS -eq 0 -o $TABS -eq 2 ]; then

fields="  dbid           number, 
          sample_time     date,
          session_id      number,
          session_state   varchar2(20),
          session_serial# number,
          user_id         number,
          sql_address     varchar2(20),
          sql_id          number,
          sql_opcode      number,
          session_type    number,
          event#          number,
          seq#            number,
          p1              number,
          p2              number,
          p3              number,
          wait_time       number,
          current_obj#   number,
          current_file#  number,
          current_block# number,
          program         varchar2(64),
          module          number,
          action          number, 
          FIXED_TABLE_SEQUENCE number,
          sample_id       number  "

TNS="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$REPO_HOST)(PORT=$REPO_PORT)))(CONNECT_DATA=(SID=$REPO_SID)))"

# create PARTITIONS v$ash_1 to v$ash_7
  while [ $x -le $nparts ]; do
    echo "create table v\$ash_${x} ("
    echo $fields
    echo ");"
    x=`expr $x + 1`
  done > $CREATE
  while [ $x -le $nparts ]; do
    echo "drop table v\$ash_${x};" 
    x=`expr $x + 1`
  done > $DROP

# create V$ASH_SQLTEXT, V$ASH_SQLSTATS, V$ASH_OBJS
  for i in 1; do
   echo "create table v\$ash_sqltxt "  
   echo "   ( dbid number, "
   echo "     address raw(8)," 
   echo "     hash_value number, "
   echo "     command_type number, "
   echo "     piece number, "
   echo "     sql_text varchar(64));"
   echo "create unique index v\$ash_sqltxt_i on v\$ash_sqltxt "
   echo "     (hash_value,"
   echo "      piece);"
   echo "create table v\$ash_sqlstats ( "
   echo "     dbid number, "
   echo "     sample_time date, "
   echo "     address raw(8), "
   echo "     hash_value number, "
   echo "     executions number, "
   echo "     elapsed_time number, "
   echo "     rows_processed number); "
   echo "create table v\$ash_objs ("
   echo "     dbid number,  "
   echo "     object_id number, "
   echo "     owner varchar2(30), "
   echo "     object_name varchar2(128), "
   echo "     subobject_name varchar2(30), "
   echo "     object_type varchar2(18));"
   echo "create unique index v\$ash_objs_i on v\$ash_objs "
   echo "     (object_id);"
  done >> $CREATE

# create DATABASE LINK
  echo 'DROP DATABASE LINK "REPO";' > $DBLINK
  for i in 1 ; do
    echo 'CREATE DATABASE LINK "REPO"'
    echo "CONNECT TO \"$REPO_SCHEMA\""
    echo "IDENTIFIED BY \"$REPO_PW\""
    echo 'USING'
    echo "'$TNS';"
  done >> $DBLINK

# create V$ASH
  for i in 1; do
    echo "create view v\$ash as " 
    while [ $x -lt $nparts ]; do
      echo " select * from v\$ash_${x} union all " 
      x=`expr $x + 1`
    done 
    echo " select * from v\$ash_${nparts} ;"  
  done >> $CREATE
  echo "drop view v\$ash ;" >> $DROP
# V$ASH_ALL is a bit redundant
# but I started recreating v$ash to be a view
# on particular hosts so I wouldn't need dbid in the queries
# this its nice to have a view that is stable, thus v$ash_all
# create V$ASH_ALL
  for i in 1; do
    echo "create view v\$ash_all as " 
    while [ $x -lt $nparts ]; do
      echo " select * from v\$ash_${x} union all " 
      x=`expr $x + 1`
    done 
    echo " select * from v\$ash_${nparts} ;"  
  done >> $CREATE
  echo "drop view v\$ash ;" >> $DROP

for i in 1; do
  echo 'create table dbids ('
  echo '    dbid number,'
  echo '    host varchar2(30),'
  echo '    home varchar2(100),'
  echo '    sid  varchar2(10),'
  echo '    ashseq number'
  echo ');'
  # echo insert into dbids values 
done  >>  $CREATE
  echo 'drop table dbids;' >> $DROP


echo "sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} "
sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} << EOF
  --@${DROP}
  @${CREATE}
  exit
EOF
fi

if [ $TABS -eq 1 -o $TABS -eq 2 ]; then

sqlplus "$SCHEMA/$PW" << EOF
  
  @${DBLINK}

  -- don't drop incase it's used by a currently running collection
  -- otherwise start reusing the same values
  --drop sequence ashseq;
  create sequence ashseq ;

  -- the following two lines are a hack
  -- I need the v$ash%rowtype but it's on the REPO
  -- I could try using rowtype across a dblink but havne't yet
  -- if there already is a v$ash view then this a problem
  view v\$ash;
  create view v\$ash as select * from $REPO_SCHEMA.v\$ash${DBL} where rownum < 1;

  drop view v\$ashnow;
  create view v\$ashnow as 
              Select
                 d.dbid,
                 sysdate sample_time,
                 s.indx          "SESSION_ID",
                 decode(w.ksusstim, 0,'WAITING','ON CPU') "SESSION_STATE",
                 s.ksuseser      "SESSION_SERIAL#",
                 s.ksuudlui      "USER_ID",
                 s.ksusesql      "SQL_ADDRESS",
                 s.ksusesqh      "SQL_ID" ,
                 s.ksuudoct      "SQL_OPCODE"  /* 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      "CURRENT_OBJ#",
                 s.ksusefil      "CURRENT_FILE#",
                 s.ksuseblk      "CURRENT_BLOCK#",
                 s.ksusepnm      "PROGRAM",
                 s.ksuseaph      "MODULE_HASH",  /* ASH collects string */
                 s.ksuseach      "ACTION_HASH",   /* ASH collects string */
                 s.ksusefix      "FIXED_TABLE_SEQUENCE" /* FIXED_TABLE_SEQUENCE */
       from
               sys.x\$ksuse s ,
               sys.x\$ksusecst w,
               v\$database d
       where
               s.indx != ( select distinct sid from v\$mystat ) and
               bitand(s.ksspaflg,1)!=0 and
               bitand(s.ksuseflg,1)!=0 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'
                            )
                       )
              );

CREATE OR REPLACE PACKAGE ash AS
--          PROCEDURE print (sleep number, loops number) ;
--        PROCEDURE get_objs  ;
--        PROCEDURE get_sqltxt  ;
--        PROCEDURE get_sqlstats  ;
          PROCEDURE collect (sleep number, loops number) ;
          PROCEDURE purge (part number) ;
          PROCEDURE purgeall  ;
          FUNCTION get_dbid  return number ;
          PROCEDURE set_dbid  ;
          END ash;
/
show errors

CREATE OR REPLACE PACKAGE BODY ash AS

       FUNCTION get_dbid return number is
          l_dbid number;
          begin
            select dbid into l_dbid from sys.v\$database;
            return l_dbid;
            -- return 1;
       end get_dbid;

       PROCEDURE set_dbid is
          l_dbid number;
          begin
            select dbid into l_dbid from sys.v\$database;
            insert into ${REPO_SCHEMA}.dbids${DBL} values (l_dbid,'${TARG_HOST}','${TARG_HOME}','${TARG_SID}',0); 
            commit;
       end set_dbid;

       PROCEDURE purgeall is
          PRAGMA AUTONOMOUS_TRANSACTION;
       begin
         for i in 1..$nparts loop
            purge(i);
         end loop; 
       end purgeall;

/*
       PROCEDURE get_objs(part number) is
          l_dbid number;
       begin
         l_dbid:=get_dbid;
         -- get object info  for top 20 sql in ASH over the last hour
         insert into  ${REPO_SCHEMA}.v\$ash_objs${DBL}
                select l_dbid, 
                       o.object_id,
                       o.owner,
                       o.object_name,
                       o.subobject_name,
                       o.object_type
                from dba_objects o
                where object_id  in ( 
                        select current_obj# from (
                        select count(*) cnt, CURRENT_OBJ#
                        from ${REPO_SCHEMA}.v\$ash_all${DBL} 
                        where
                               l_dbid = db_id
                           and current_obj# > 0
                           and sample_time > (sysdate - 1/24)
                        group by current_obj#
                        order by cnt )
                     where rownum < 21)
                  and object_id not in (select object_id from 
                       ${REPO_SCHEMA}.v\$ash_objs${DBL} 
                       where l_dbid = dbid) ;
         commit;
       end get_objs;

       PROCEDURE get_sqlstats is
          l_dbid number;
       begin
         l_dbid:=get_dbid;
         -- get sql stats  for top 20 sql in ASH over the last hour
         insert into  ${REPO_SCHEMA}.v\$ash_sqlstats${DBL} 
                select l_dbid,
                       sysdate,
                       sql.address,
                       sql.hash_value,
                       sql.executions,
                       sql.elapsed_time,
                       sql.rows_processed 
                from gv\$sql sql
                where sql.hash_value in ( 
                       select  hash_value
                       from ${REPO_SCHEMA}.v\$ash_sqltxt${DBL} 
                       where l_dbid = dbid );
         commit;
       end get_sqlstats;

       PROCEDURE get_sqltxt is
          l_dbid number;
       begin
         l_dbid:=get_dbid;
         -- get sqltext for top 20 sql in ASH over the last hour
         insert into  ${REPO_SCHEMA}.v\$ash_sqltxt${DBL} 
                select l_dbid, 
                       sqlt.address,
                       sqlt.hash_value,
                       sqlt.command_type,
                       sqlt.piece,
                       sqlt.sql_text
                from gv\$sqltext sqlt
                where sqlt.hash_value in ( 
                        select sql_id from (
                          select count(*) cnt, sql_id
                          from ${REPO_SCHEMA}.v\$ash_all${DBL} 
                          where l_dbid = dbid
                             and sample_time > (sysdate - 1/24)
                          group by sql_id
                          order by cnt )
                        where rownum < 21)
                  and hash_value not in (
                       select hash_value 
                       from ${REPO_SCHEMA}.v\$ash_sqltxt${DBL} 
                       where l_dbid = dbid);
         commit;
       end get_sqlstats;
       end get_sqltxt;

       PROCEDURE purge(part number) is
          PRAGMA AUTONOMOUS_TRANSACTION;
          l_text varchar2(200);
       begin
         l_text:='truncate table v\$ash_'||to_char(part)||' reuse storage';
         dbms_output.put_line(l_text);
         execute immediate l_text;
       end purge;

/*
       PROCEDURE print(sleep number, loops number) is
          ash_rec v\$ash%rowtype;
          cursor ash_cur
             return v\$ash%ROWTYPE is  
               select a.*, ashseq.nextval sample_id from v\$ashnow a;
          begin
            for i in 1..loops loop
              open ash_cur; loop
                fetch ash_cur into ash_rec;
                exit when ash_cur%notfound;
                dbms_output.put_line(ash_rec.sample_time||' '||
                                     to_char(ash_rec.session_id)||' '||
                                     ash_rec.session_state);
              end loop;
              dbms_lock.sleep(sleep);
              close ash_cur;
            end loop;
       end print;
*/

       PROCEDURE collect(sleep number, loops number) is
          ash_rec v\$ash%rowtype;
          l_dbid number;
          cpart    number := -1;      /* current partition number */
          part     number := 1;       /* new partition number */
          npart    number := $nparts; /* current partition number */
          cur_ashseq   number := 0;
          cursor ash_cur
             return v\$ash%ROWTYPE is  
               select a.*, cur_ashseq sample_time from v\$ashnow a;
          begin
            l_dbid:=get_dbid;
            for i in 1..loops loop
              select  ashseq.nextval into cur_ashseq from dual;
              --
              update  ${REPO_SCHEMA}.dbids${DBL} set ashseq = cur_ashseq
              where dbid = l_dbid;
              dbms_output.put_line('loop # '||to_char(i));
              -- change partitions every day of the week  1-7 , SUN = 1
              select to_number(to_char(sysdate,'D')) into part from dual;
              if part != cpart then
                 -- don't purge the first time around incase data exists from previous run
                 if cpart != -1 then
                    purge(part);
                 end if;
                 cpart:=part;
              end if; 
              open ash_cur; loop
                fetch ash_cur into ash_rec;
                exit when ash_cur%notfound;
                -- ie if sample_id not great than 1 then its not real data, don't insert
                if ash_rec.sample_id > 1 then
                  dbms_output.put_line('insert into part '||to_char(part));
                  if part = 1 then insert into ${REPO_SCHEMA}.v\$ash_1${DBL} values ash_rec; end if;
                  if part = 2 then insert into ${REPO_SCHEMA}.v\$ash_2${DBL} values ash_rec; end if;
                  if part = 3 then insert into ${REPO_SCHEMA}.v\$ash_3${DBL} values ash_rec; end if;
                  if part = 4 then insert into ${REPO_SCHEMA}.v\$ash_4${DBL} values ash_rec; end if;
                  if part = 5 then insert into ${REPO_SCHEMA}.v\$ash_5${DBL} values ash_rec; end if;
                  if part = 6 then insert into ${REPO_SCHEMA}.v\$ash_6${DBL} values ash_rec; end if;
                  if part = 7 then insert into ${REPO_SCHEMA}.v\$ash_7${DBL} values ash_rec; end if;
                end if;
              end loop;
              close ash_cur;
              commit;
              dbms_lock.sleep(sleep);
            end loop;
       end collect;

END ash;
/

show errors

exec ash.set_dbid;

exit
EOF
fi