Tip#5 DDL Auditing

As a DBA , I sometimes get complains that this object is missing or changed on our development or UAT system. So in order to find out WHO is the culprit I need to setup the DDL auditing. This is how I generally do this,

STEP 1 :
Please note that you need to give the user (which you are going to use) the following grant,

GRANT ADMINISTER DATABASE TRIGGER TO username;

STEP 2 :
Create Table to log the changes, something like following

CREATE TABLE DDL_AUDIT
(
AUD_ID NUMBER,
USERNAME VARCHAR2(50 BYTE),
HOSTNAME VARCHAR2(50 BYTE),
DDL_DATE DATE,
OBJ_TYPE VARCHAR2(50 BYTE),
OBJ_OWNER VARCHAR2(30 BYTE),
OBJ_NAME VARCHAR2(30 BYTE),
DDL_EVENT VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
/

STEP 3 :
Create sequence for the PK of the table,

CREATE SEQUENCE DDL_AUDIT_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER
/

STEP 4 :
Create a DDL auditing database trigger,

CREATE OR REPLACE TRIGGER ddl_audit_trigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO ddl_audit
(aud_id, username,
hostname, ddl_date, obj_type, obj_owner,
obj_name, ddl_event
)
VALUES (ddl_audit_seq.NEXTVAL, SYS_CONTEXT ('USERENV', 'OS_USER'),
REPLACE (SYS_CONTEXT ('USERENV', 'HOST'), 'your_domain\', ''), SYSDATE,
ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent
);
EXCEPTION
WHEN OTHERS
THEN
-- log in the error table or ignore it
NULL; -- ignore it
END;
/