Oracle System Event Triggers

General
Data Dictionary Objects	dba_triggers all_triggers user_triggers trigger$
Related System Privileges	administer database trigger alter any trigger create trigger create any trigger drop any trigger
System Event Trigger Types	AFTER STARTUP BEFORE SHUTDOWN AFTER LOGON BEFORE LOGOFF AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover AFTER SUSPEND AFTER SERVERERROR (does not trap ... ORA-01403: no data found (this is in the Oracle docs but does not seem to be correct) ORA-01422: exact fetch returns more than requested number of rows ORA-01423: error encountered while checking for extra rows in exact fetch ORA-01034: ORACLE not available ORA-04030: out of process memory when trying to allocate string bytes (string, string)
Database Level Event Triggers	SELECT a.obj#, a.sys_evts, b.name FROM trigger$ a,obj$ b WHERE a.sys_evts > 0 AND a.obj#=b.obj# AND baseobject = 0;
Schema Level Event Triggers	SELECT a.obj#, a.sys_evts, b.name FROM trigger$ a,obj$ b WHERE a.sys_evts > 0 AND a.obj#=b.obj# AND baseobject = 88;
Disabling System Triggers	If there is an error in a system trigger, for example an AFTER STARTUP trigger, it may be impossible to start the database. The following is the method for disabling system triggers.
	/ as sysdba set linesize 150 col NAME format a30 col VALUE format a20 col DESCRIPTION format a60 SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION FROM x$ksppi x, x$ksppcv y WHERE x.inst_id = userenv('Instance') AND y.inst_id = userenv('Instance') AND x.indx = y.indx AND x.ksppinm = '_system_trig_enabled'; ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;
Create SYSTEM EVENT TRIGGER
System Trigger Demo Demo table and the logproc procedure (below) must be built before the trigger will compile	CREATE OR REPLACE TRIGGER ON DATABASE CALL /
	CREATE TABLE connection_audit ( login_date DATE, user_name VARCHAR2(30)); CREATE OR REPLACE PROCEDURE logproc IS BEGIN INSERT INTO connection_audit (login_date, user_name) VALUES (SYSDATE, USER); END logproc; / CREATE OR REPLACE TRIGGER logintrig AFTER LOGON ON DATABASE CALL logproc / conn sh/sh conn scott/tiger conn uwclass/uwclass SELECT * FROM connection_audit; drop trigger logintrig;
Demo Application To Log Logon Attempts ... Both Successful And Failed	CREATE OR REPLACE TRIGGER ON DATABASE END ; /
	truncate table connection_audit; -- trigger to trap successful logons CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE BEGIN INSERT INTO connection_audit (login_date, user_name) VALUES (SYSDATE, USER); END logon_audit; / conn scott/tiger conn sh/sh conn / as sysdba conn uwclass/uwclass SELECT * FROM connection_audit; -- trigger to trap unsuccessful logons CREATE OR REPLACE TRIGGER logon_failures AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR(1017)) THEN INSERT INTO connection_audit (login_date, user_name) VALUES (SYSDATE, 'ORA-1017'); END IF; END logon_failures; / conn scott/tigre conn abc/def conn test/test conn uwclass/uwclass SELECT * FROM connection_audit; /* other errors that could be trapped include: ORA-01004 - default username feature not supported ORA-01005 - null password given ORA-01035 - Oracle only available to users with restricted session priv ORA-01045 - create session privilege not granted */
Demo To Log System Errors	CREATE TABLE servererror_log ( error_datetime TIMESTAMP, error_user VARCHAR2(30), db_name VARCHAR2(9), error_stack VARCHAR2(2000), captured_sql VARCHAR2(1000)); CREATE OR REPLACE TRIGGER log_server_errors AFTER SERVERERROR ON DATABASE DECLARE captured_sql VARCHAR2(1000); BEGIN SELECT q.sql_text INTO captured_sql FROM gv$sql q, gv$sql_cursor c, gv$session s WHERE s.audsid = audsid AND s.prev_sql_addr = q.address AND q.address = c.parent_handle; INSERT INTO servererror_log (error_datetime, error_user, db_name, error_stack, captured_sql) VALUES (systimestamp, sys.login_user, sys.database_name, dbms_utility.format_error_stack, captured_sql); END log_server_errors; /
After Logon Trigger for Tracing	CREATE OR REPLACE TRIGGER trace_trig AFTER LOGON ON DATABASE DECLARE sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'''; BEGIN IF (USER = 'UWCLASS') THEN execute immediate sqlstr; END IF; END trace_trig; /
After Logon Trigger for Outlines	CREATE OR REPLACE TRIGGER trace_trig AFTER LOGON ON DATABASE DECLARE sqlstr VARCHAR2(200) := 'alter session set use_stored_outlines = uw_outlines'; BEGIN IF (USER = 'UWCLASS') THEN execute immediate sqlstr; END IF; END trace_trig; /
Drop SYSTEM EVENT TRIGGER
Drop Trigger	DROP TRIGGER ;
	DROP TRIGGER logon_failures;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9907339/viewspace-1047085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9907339/viewspace-1047085/