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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. A small typo exists in this post atm,
    in table creation script there is
    column name SQL_TEXT but in trigger creation script there is “insert into ..values(sql_txt)

Leave a Reply

Your email address will not be published. Required fields are marked *