ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL log.
The DDL log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to true. When this parameter is set to false, DDL statements are not included in any log.
The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages.
There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show parameter enable_ddl_loggingshow parameter enable_ddl_logging
SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=TRUE scope=both;
System altered.
SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean TRUE
The DDL log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to true. When this parameter is set to false, DDL statements are not included in any log.
The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages.
There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show parameter enable_ddl_loggingshow parameter enable_ddl_logging
SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=TRUE scope=both;
System altered.
SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean TRUE
SQL> select value from v$diag_info where name='ADR Home';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/12.1.0/admin/diag/rdbms
SQL> select value from v$diag_info where name='ADR Home';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle//12.1.0/admin//diag/rdbms/
SQL> create table test(name number);
Table created.
[oracle@test26 ddl]$ pwd
/u01/app/oracle//12.1.0/admin/diag/rdbms/log/ddl
[oracle@test26 ddl]$ cat log.xml
<msg time='2017-02-28T18:01:57.657+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4407:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='srv-vimats26-int.com' host_addr='172.17.1.226'
version='1'>
<txt> create table test(name number)
</txt>
</msg>