rem awr_snap_trigger.sql
rem (c) Go-Faster Consultancy Ltd. 2008

spool awr_snap_trigger

rollback;
----------------------------------------------------------------
rem requires following grants to be made explicitly by sys
GRANT EXECUTE ON sys.dbms_workload_repository TO sysadm;
--EXECUTE SYS.DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
----------------------------------------------------------------

CREATE TABLE gfc_awr_snap_log
(prcsinstance NUMBER NOT NULL
,runstatus    NUMBER NOT NULL  
,snap_id      NUMBER NOT NULL
);

CREATE OR REPLACE TRIGGER sysadm.awr_snap
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (   (   (new.runstatus  = 7 AND old.runstatus != 7)
         OR  (new.runstatus != 7 AND old.runstatus  = 7)
         )
----------------------------------------------------------------
--and criteria to control which processes collect snapshots.
--here all Jobs and any process where the run control is like %AWR%SNAP%
----------------------------------------------------------------
     AND (    new.prcstype = 'PSJob'
         OR   UPPER(new.runcntlid) LIKE '%AWR%SNAP%'
----------------------------------------------------------------
         )
     )
DECLARE
   l_flush_level VARCHAR2(16);
   l_snap_id NUMBER;
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   IF :new.runstatus = 7 THEN
      l_flush_level := 'TYPICAL';
   ELSE
      l_flush_level := 'ALL';
   END IF;

   l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level => l_flush_level);

   INSERT INTO gfc_awr_snap_log
   (prcsinstance, runstatus, snap_id)
   VALUES
   (:new.prcsinstance, :new.runstatus, l_snap_id);

   COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;
END;
/

show errors

----------------------------------------------------------------

rem test that the trigger fires by updating something
UPDATE  sysadm.psprcsrqst
SET     runstatus = 7
WHERE   runstatus != 7
AND     prcstype = 'PSJob'
AND     rownum = 1
/

ROLLBACK
/

SELECT	*
FROM 	dba_hist_snapshot
WHERE	end_interval_time > SYSDATE - .01
/

spool off