rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Virag S.
rem *                     John Strother, Craig Shallahamer, Mike Jondrow
rem * Original          : 17-APR-95
rem * Last Update       : %H% - %T%
rem * Description       : Load session wait information
rem * Usage             : start swl.sql <tty> <run id>
rem * Example           : E.g. tty  : pts/69
rem *                          runid: whatever number you wish
rem ********************************************************************

def terminal=&&1
def runid=&&2

set heading off
select 'Loading session wait data for terminal &terminal, runid &runid...'
from   dual;

set heading on

declare
  tty varchar2(40) := '&terminal';
  rid varchar2(40) := '&runid';
  p1 exception;
  pragma exception_init(p1,-1013);
begin
  loop
    insert into aps_session_wait_log
        ( SID,SEQ#,EVENT,P1TEXT,P1,P2TEXT
         ,P2,P3TEXT,P3,WAIT_TIME,CR_DATE,runid)
      select a.*,sysdate CR_DATE, &runid
        from v$session_wait a
       where sid in
         (select sid from v$session where terminal = tty);
  end loop;
exception when p1 then
  null;
end;

select event,sid,count(*) from aps_session_wait_log
group by event,sid;

