DDL Audit trigger that sends e-mail

Found this code on google and modified it to send e-mail when DDL change on schema of interest.You can alter it to fit your needs..

First create two tables that will store DDL information:events and sqltext:
DROP TABLE ddl_events;
CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30),
  ip_address       VARCHAR2(20));
 
DROP TABLE ddl_events_sql;
CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );
 
Sequence to support events id’s:
DROP SEQUENCE dsq_ddlEvents;
CREATE SEQUENCE dsq_ddlEvents START WITH 1000;
and here is trigger code:

CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE
 
  l_sqlText    ORA_NAME_LIST_T;
 
BEGIN
  IF ORA_DICT_OBJ_OWNER in (‘SCHEMA_NAME’)
  THEN
BEGIN
  utl_mail.send (
  sender => ‘senders_email’,
  recipients => ‘recipient_email’,
  subject => ‘DDL change  has been made in ‘||ORA_DATABASE_NAME||’ database.’,
  message => ‘User ‘||ORA_LOGIN_USER||’ had run ‘||ORA_SYSEVENT|| ‘ on ‘||ORA_DICT_OBJ_TYPE||’ ‘||ORA_DICT_OBJ_OWNER||’.’||ORA_DICT_OBJ_NAME||’ in ‘||ORA_DATABASE_NAME||’ database.’
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’)
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT(‘USERENV’,’SESSIONID’ ) = audsid(+) );
 
   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/

Keep in mind purging recyclebin with objects owned by schema will also send e-mail so you might need to exclude BIN objects.