DDL trigger

Sometimes someone creates/drop/modify db object(s). However it depends requester. To prevent this dislike events I suggest create DDL trigger. This trigger will fire every DDL action.

First of all we should determine which kind of DDL we have to store. So, create table:

CREATE TABLE SYS.DDL_WATCH_LOG
(
  ACTION             CHAR(6 BYTE),
  ORA_EVENT       VARCHAR2(20 BYTE),
  ORACLE_LOGIN_USER      VARCHAR2(30 BYTE),
  ORA_DATABASE_NAME   VARCHAR2(50 BYTE),
  ORA_OBJECT_TYPE   VARCHAR2(20 BYTE),
  ORA_OBJECT_NAME   VARCHAR2(30 BYTE),
  ORA_OBJECT_OWNER  VARCHAR2(30 BYTE),
  ORA_SESSIONUSER        VARCHAR2(30 BYTE),
  ORA_HOSTNAME           VARCHAR2(60 BYTE),
  ORA_IPADDRESS          VARCHAR2(30 BYTE),
  OSUSER             VARCHAR2(30 BYTE),
  DDL_TEXT           CLOB,
  DDL_DATE           TIMESTAMP(6) WITH LOCAL TIME ZONE
)

Now, create trigger:

CREATE OR REPLACE TRIGGER DDL_WATCH_AFTER
AFTER DDL ON DATABASE
BEGIN
IF ora_dict_obj_type IN ('TABLE','INDEX'/*an other object types*/) THEN /*You may exclude specific db users*/

INSERT INTO DDL_WATCH_LOG (ACTION, ORA_EVENT, ORACLE_LOGIN_USER, ORA_DATABASE_NAME, ORA_OBJECT_TYPE, ORA_OBJECT_NAME, ORADICT_OBJ_OWNER, ORA_SESSIONUSER, ORA_HOSTNAME, ORA_IPADDRESS, OSUSER, OSUSER, DDL_TEXT, DDL_DATE)
VALUES
('AFTER', ora_sysevent, ora_login_user, ora_database_name, ora_dict_obj_type, ora_dict_obj_name, sys_context('userenv','SESSION_USER'), sys_context('userenv','HOST'),
sys_context('userenv','IP_ADDRESS'), sys_context('userenv','OS_USER'),Dbms_Metadata.GET_DDL(ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner), SYSDATE);
END IF;
END;
/



Has another method, example using audit, but this trigger will behavior on development DBs where several database users changes existing db objects, create/remove etc.