Log DDL Changes in Your Schema

A lot of developers or DBA do a lot of  DDL operations on same schema
So we should create logging for this DDL operations in database table.

At first this post depends on my previous post Oracle System Events and Client Events

I should store all below data in logging

I will create table to store data of logging and I suggested below attributes 
1- DDL operation date
2- DB Username
3- Operating System Username
4- Machine that command executed from it.
5- Terminal that command executed from it.
6- DDL Operation Type (Create,Drop,Alter,...)
7- Database Object Type (Table, View, ......)
8- Database Object Name

Script to Create Table

  CREATE TABLE DDL_USER_LOGS
(
   OPERATION_DATE   DATE,          --1- DDL operation date
   USERNAME         VARCHAR2 (30), --2- DB Username
   OSUSER           VARCHAR2 (30), --3- Operating System Username
   MACHINE          VARCHAR2 (30), --4- Machine that command executed from it.
   TERMINAL         VARCHAR2 (30), --5- Terminal that command executed from it.
   OPERATION        VARCHAR2 (30), --6- DDL Operation Type (Create,Drop,Alter,...)
   OBJTYPE          VARCHAR2 (30), --7- Database Object Type (Table, View, ......)
   OBJNAME          VARCHAR2 (30)  --8- Database Object Nam
);

Then, I will create AUDIT_DDL_CHANGES trigger on Schema for logging DDL operations and insert it in new table DDL_USER_LOGS

CREATE OR REPLACE TRIGGER AUDIT_DDL_CHANGES
   AFTER CREATE OR DROP OR ALTER
   ON BUSS.SCHEMA    -- Change BUSS to your schema name!!!
BEGIN
   INSERT INTO DDL_USER_LOGS (OPERATION_DATE,
                              USERNAME,
                              OSUSER,
                              MACHINE,
                              TERMINAL,
                              OPERATION,
                              OBJTYPE,
                              OBJNAME)
        VALUES (SYSDATE,
                SYS_CONTEXT ('USERENV', 'SESSION_USER'),
                SYS_CONTEXT ('USERENV', 'OS_USER'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                SYS_CONTEXT ('USERENV', 'TERMINAL'),
                ORA_SYSEVENT,
                ORA_DICT_OBJ_TYPE,
                ORA_DICT_OBJ_NAME);
END;

Let's now test our demo
I will execute below DDL in BUSS user

CREATE TABLE MY_TABLE (MY_COL1 NUMBER);

DROP TABLE MY_TABLE;

After running create table and drop table script select data from log table and watch result.

SELECT * FROM DDL_USER_LOGS;

The result data is like below( Note this another data will be retrieved in your machine)

Thanks
Mahmoud Ahmed El-Sayed