Wednesday, October 30, 2013

How to create a DDL trigger in a schema



CREATE TABLE AUDIT_DDL (
 d date,
 OSUSER varchar2(255),
 CURRENT_USER varchar2(255),
 HOST varchar2(255),
 TERMINAL varchar2(255),
 owner varchar2(30),
 type varchar2(30),
 name varchar2(30),
 sysevent varchar2(30));


connect uid/pwd
CREATE OR REPLACE TRIGGER audit_ddl_trg after ddl on schema
BEGIN
IF(ora_sysevent='TRUNCATE') THEN
 null; -- If we do not care about truncate
ELSE
 INSERT INTO audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
 values(
  sysdate,
  sys_context('USERENV','OS_USER') ,
  sys_context('USERENV','CURRENT_USER') ,
  sys_context('USERENV','HOST') , 
  sys_context('USERENV','TERMINAL') ,
  ora_dict_obj_owner,
  ora_dict_obj_type,
  ora_dict_obj_name,
  ora_sysevent
  );
 END IF;
END;
/


Source: Don Burleson http://www.dba-oracle.com/t_ddl_triggers.htm

No comments:

Post a Comment