How To Enable DDL Auditing in Oracle 11g and 12c(enable_ddl_logging)

Enable DDL Auditing in 11g

It is possible to enable ddl auditing in a database or shema. In this way, we can monitor all DDL changes and we can undo the wrong operation in case of a problem.

Create a table and trigger to audit all DDL operations in the database.

Create a Table for DDL Auditing

Create a Trigger For DDL Auditing

The following trigger will enable DDL auditing at the entire database level. If you write “on schema” instead of “on database” in the trigger, it will enable DDL auditing for only the user who created the trigger.

Enable DDL Auditing in 12c

It is possible to enable DDL auditing for Oracle 11g and earlier versions by performing the above operations.

Oracle introduced a new feature with 12c; now it is possible to enable DDL auditing without needing these operations.

When the ENABLE_DDL_LOGGING parameter is enabled, DDL changes can be logged in the following directory as log and xml file.

The parameter ENABLE_DDL_LOGGING can be changed as follows.

After the parameter change, in the first ddl operation, a file such as log.xml is created under the $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl directory and all the DDL operations are written as follows.