| < Day Day Up > |
|
-- MV_CaptureSessionWaits.sql -- This script contains all the objects required to capture -- user session information on session logout from -- gv$session_wait -- -- This will only work with Oracle Real Application Clusters -- NOTE: The script assumes that the user MVPERFAUDIT is present -- Author: Murali Vallath -- Date: 01-SEP-2002 -- Execute this procedure logged in as sys or system -- SET ECHO ON SET SPOOL MV_CaptureSessionWaits.lis -- -- Drop the table if already present -- DROP TABLE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT; -- -- Create the table to store the session wait information -- CREATE TABLE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT ( MVASW_SEQ NUMBER ,MVASW_INST_ID NUMBER ,MVASW_SID NUMBER ,MVASW_USERNAME VARCHAR2(30) ,MVASW_MACHINE VARCHAR2(64) ,MVASW_OSUSER VARCHAR2(30) ,MVASW_MODULE VARCHAR2(48) ,MVASW_SQL_ADDRESS RAW(8) ,MVASW_SEQ# NUMBER ,MVASW_EVENT VARCHAR2(64) ,MVASW_P1TEXT VARCHAR2(64) ,MVASW_P1 NUMBER ,MVASW_P1RAW RAW(8) ,MVASW_P2TEXT VARCHAR2(64) ,MVASW_P2 NUMBER ,MVASW_P2RAW RAW(8) ,MVASW_P3TEXT VARCHAR2(64) ,MVASW_P3 NUMBER ,MVASW_P3RAW RAW(8) ,MVASW_WAIT_TIME NUMBER ,MVASW_SECONDS_IN_WAIT NUMBER ,MVASW_STATE VARCHAR2(19) ,MVASW_FAILOVER_TYPE VARCHAR2(13) ,MVASW_FAILOVER_METHOD VARCHAR2(10) ,MVASW_FAILED_OVER VARCHAR2(3) ,MVASW_SESSION_WAIT_DATE_TIME DATE DEFAULT SYSDATE ) TABLESPACE TOOLS / -- -- Create the public synonym for the table and other privileges -- CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_WAIT for MVPERFAUDIT.MV_AUDIT_SESSION_WAIT; GRANT INSERT,SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_WAIT TO PUBLIC; -- Create sequence and grant rights to users -- CREATE SEQUENCE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 200 ORDER / CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_WAIT_SEQ FOR MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ; GRANT SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ TO PUBLIC; -- -- Create the trigger to capture session wait statistics -- CREATE OR REPLACE TRIGGER MV_AUDIT_SESSION_WAIT_TRG BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO MV_AUDIT_SESSION_WAIT SELECT MV_AUDIT_SESSION_WAIT_SEQ.NEXTVAL ,GVSW.INST_ID ,GVSW.SID ,GVS.USERNAME ,GVS.MACHINE ,GVS.OSUSER ,GVS.MODULE ,GVS.SQL_ADDRESS ,GVSW.SEQ# ,GVSW.EVENT ,GVSW.P1TEXT ,GVSW.P1 ,GVSW.P1RAW ,GVSW.P2TEXT ,GVSW.P2 ,GVSW.P2RAW ,GVSW.P3TEXT ,GVSW.P3 ,GVSW.P3RAW ,GVSW.WAIT_TIME ,GVSW.SECONDS_IN_WAIT ,GVSW.STATE ,GVS.FAILOVER_TYPE ,GVS.FAILOVER_METHOD ,GVS.FAILED_OVER ,SYSDATE FROM GV$SESSION_WAIT GVSW, GV$SESSION GVS WHERE GVSW.SID = GVS.SID AND GVSW.INST_ID = GVS.INST_ID AND GVS.AUDSID = userenv(’sessionid’); END; ========================================================= -- MV_CaptureSessionEvents.sql -- This script contains all the objects required to capture -- user session information on logout from gv$session_event -- -- This will only work with Oracle Real Application Clusters -- NOTE: The script assumes that the user MVPERFAUDIT is present -- Author: Murali Vallath -- Date: 01-SEP-2002 -- Execute this procedure logged in as sys or system -- SET ECHO ON SET SPOOL MV_CaptureSessionEvents.lis -- -- Drop the table if already present -- DROP TABLE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT; -- -- Create the table to store the session event information -- CREATE TABLE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT ( MVASE_ID NUMBER ,MVASE_INST_ID NUMBER ,MVASE_SID NUMBER ,MVASE_USERNAME VARCHAR2(30) ,MVASE_MACHINE VARCHAR2(64) ,MVASE_OSUSER VARCHAR2(30) ,MVASE_MODULE VARCHAR2(48) ,MVASE_SQL_ADDRESS RAW(8) ,MVASE_EVENT VARCHAR2(64) ,MVASE_TOTAL_WAITS NUMBER ,MVASE_TOTAL_TIMEOUTS NUMBER ,MVASE_TIME_WAITED NUMBER ,MVASE_AVERAGE_WAIT NUMBER ,MVASE_MAX_WAIT NUMBER ,MVASE_TIME_WAITED_MICRO NUMBER ,MVASE_FAILOVER_TYPE VARCHAR2(13) ,MVASE_FAILOVER_METHOD VARCHAR2(10) ,MVASE_FAILED_OVER VARCHAR2(3) ,MVASE_SESSION_DATE_TIME DATE DEFAULT SYSDATE ) TABLESPACE TOOLS / CREATE SEQUENCE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 200 ORDER / CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_EVENT for MVPERFAUDIT.MV_AUDIT_SESSION_EVENT; CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_EVENT_SEQ FOR MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ; GRANT INSERT,SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_EVENT TO PUBLIC; GRANT SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ TO PUBLIC; -- -- Create the logout trigger to capture session event statistics -- CREATE OR REPLACE TRIGGER MV_PERFAUDIT_SESSION_EVENT_TRG BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO MV_AUDIT_SESSION_EVENT SELECT MV_AUDIT_SESSION_EVENT_SEQ.NEXTVAL, GVSE.INST_ID ,GVSE.SID ,GVS.USERNAME ,GVS.MACHINE ,GVS.OSUSER ,GVS.MODULE ,GVS.SQL_ADDRESS ,GVSE.EVENT ,GVSE.TOTAL_WAITS ,GVSE.TOTAL_TIMEOUTS ,GVSE.TIME_WAITED ,GVSE.AVERAGE_WAIT ,GVSE.MAX_WAIT ,GVSE.TIME_WAITED_MICRO ,GVS.FAILOVER_TYPE ,GVS.FAILOVER_METHOD ,GVS.FAILED_OVER ,SYSDATE FROM GV$SESSION_EVENT GVSE, GV$SESSION GVS WHERE GVSE.SID = GVS.SID AND GVSE.INST_ID = GVS.INST_ID AND GVS.AUDSID = userenv(’sessionid’); END; /
| < Day Day Up > |
|