Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE AUDIT_DDL ( D DATE, OSUSER VARCHAR2 (255), SESSION_USER VARCHAR2 (255), HOST VARCHAR2 (255), TERMINAL VARCHAR2 (255), MODULE VARCHAR2 (255), OWNER VARCHAR2 (30), TYPE VARCHAR2 (30), NAME VARCHAR2 (30), SYSEVENT VARCHAR2 (30), SQL_TEXT VARCHAR2 (4000) ); |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | create or replace trigger audit_ddl_trg after ddl on database declare sql_text ora_name_list_t; stmt VARCHAR2(4000) := ''; n number; begin n:=ora_sql_txt(sql_text); for i in 1..n loop stmt:=substr(stmt||sql_text(i),1,4000); end loop; insert into audit_ddl(d, osuser,session_user,host,terminal,module,owner,type,name,sysevent,sql_txt) values( sysdate, sys_context('USERENV','OS_USER') , sys_context('USERENV','SESSION_USER') , sys_context('USERENV','HOST') , sys_context('USERENV','TERMINAL') , sys_context('USERENV','MODULE') , ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, ora_sysevent, stmt ); end; / |
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.
1 | $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl |
The parameter ENABLE_DDL_LOGGING can be changed as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> show parameter ENABLE_DDL_LOGGING NAME TYPE VALUE --------------------------------------------- enable_ddl_logging boolean FALSE SQL> alter system set enable_ddl_logging=TRUE scope=both sid='*'; System altered. NAME TYPE VALUE --------------------------------------------- enable_ddl_logging boolean TRUE |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <msg time='2018-05-11T13:40:37.219+03:00' org_id='oracle' comp_id='rdbms' msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl' level='16' host_id='db01.local' host_addr='10.10.1.120' pid='326761' version='1'> <txt>create table a(ad varchar(100)) </txt> </msg> <msg time='2018-05-11T13:55:40.162+03:00' org_id='oracle' comp_id='rdbms' msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl' level='16' host_id='db.local' host_addr='10.10.1.120' pid='393307'> <txt>alter table a add soyad varchar(100) </txt> </msg> |
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)
And it would be better if you notice also that “ENABLE_DDL_LOGGING” requires licence of “Oracle Database Lifecycle Management Pack”, and it is not so cheap.
Licensed Parameters:
The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of Oracle Database Lifecycle Management Pack for Oracle Database when set to TRUE.
-https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109-