DDL Trigger to audit schema changes

1. Create sequence for assigning numbers to the events:

   -- Create sequence

   create sequence SYS.DSQ_DDLEVENTS
   minvalue 1
   maxvalue 9999999999999999999999999999
   start with 1
   increment by 1
   cache 100;

2. Create ddl events table

   -- Create table

   create table SYS.DDL_EVENTS
   (
    eventid NUMBER,
    inst_id   NUMBER,
    eventdate DATE,
    oraloginuser VARCHAR2(100),
    oradictobjname VARCHAR2(100),
    oradictobjowner VARCHAR2(100),
    oradictobjtype VARCHAR2(100),
    orasysevent VARCHAR2(100),
    machine VARCHAR2(100),
    program VARCHAR2(100),
    osuser VARCHAR2(100)
   )
   tablespace USERS;

3. Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)

   -- Create table

   create table SYS.DDL_EVENTS_SQL
   ( eventid NUMBER,
    sqlline NUMBER,
    sqltext VARCHAR2(4000)
   )
   tablespace USERS;

4. Create DDL trigger(it doesn’t degrade performance at all, by my experience)

   CREATE OR REPLACE TRIGGER sys.audit_ddl_trg
   AFTER DDL ON DATABASE
   DECLARE
   l_eventId NUMBER;
    l_sqlText ORA_NAME_LIST_T;
   BEGIN
   if ORA_SYSEVENT!='TRUNCATE' then
   
    SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;
   INSERT INTO ddl_events (eventId,
    inst_id,
    EVENTDATE,
    ORALOGINUSER,
    ORADICTOBJNAME,
    ORADICTOBJOWNER,
    ORADICTOBJTYPE,
    ORASYSEVENT,
    machine,
    program,
    osuser
    )
    ( SELECT l_eventId,
    inst_id,
    SYSDATE,
    ORA_LOGIN_USER,
    ORA_DICT_OBJ_NAME,
    ORA_DICT_OBJ_OWNER,
    ORA_DICT_OBJ_TYPE,
    ORA_SYSEVENT,
    machine,
    program,
    osuser
    FROM SYS.DUAL
    right outer join
    SYS.GV$SESSION s on (1=1)
    WHERE s.sid=SYS_CONTEXT('USERENV','SID')
    and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id);
    FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( l_eventId, l, l_sqlText(l) );
    END LOOP;
    end if;
   
    exception when others then
    null;
   END;