Oracle 11g DDL Logging

11G introduce a New Feature which allows the DBA to log all data definition language (DDL) statements to a DDL alert log. I think that this is a usefull feature, for example you can audit a update from your software vendor to see what is happen on your database. But be carefull, this parameter needs a extra cost option for your database so it is only avaible on the Enterprise Edition of the Database. This Information cannot be found if you only look for the Parameter description like here.

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE. When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl. The default setting is FALSE.

The Information above can be found in the Oracle Database Licensing Information. In older documentations this Pack can also be named Oracle Change Management Pack.

Even this is not allowed on Standard Edition, the feature work well on it. For example if you enable it on a SE there is no warning.

Example:

SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 14 23:51:39 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter system set enable_ddl_logging=true;

System altered.

Create a Example Table:

SQL> create table ddl_testing (foo varchar2(10),bar varchar2(10));

Table created.

You will find the DDL Log unter <ORACLE_BASE>/diag/rdbms/<DB_NAME>/<INTANCE_NAME>/log

[oracle@server1 log]$ cat ddl_DB12SE_1.log 
Thu Nov 14 23:43:51 2013
diag_adl:create table ddl_testing (foo varchar2(10),bar varchar2(10))

Even this can be a powerful feature, it can be also a license pitfall so be careful what you configure on your database.