DDL Logging in Oracle

Starting Oracle 12c, DDL (Data Definition Language) command are logged in a log file which is located under <diag_location>/rdbms/<db_unique_name>/<sid>/log. In my case, it is located under c:\app\oracle\diag\rdbms\db12c\db12c\log directory, and log file name is ddl_<db_name>.log. XML version of this DDL log file is stored under
<diag_location>/rdbms/<db_unique_name>/<sid>/log/ddl. In my case is is located under c:\app\oracle\diag\rdbms\db12c\db12c\log\ddl, and file name is log.xml.

DDL logging is enabled by setting init parameter enable_ddl_logging, which is same in previous oracle releases. Following is an example of enabling and viewing the DDL logging.

Enable DDL logging from root container

SQL> alter system set enable_ddl_logging=true; System altered.

Executing DDLs (from root container any PDB)

SQL> create table test as select * from user_tables; Table created. SQL> alter table test add (time_stamp date); Table altered. SQL> create or replace trigger test_trig after insert on test     declare     a number;     begin     select count(*) into a from test;     end;     / Trigger created. SQL> drop table test; Table dropped. SQL> conn salman/salman@pdb1 Connected. SQL> create table test as select * from user_tables; Table created. SQL> conn salman/salman@pdb1 Connected. SQL> drop table test; Table dropped.

In above example, I did a re-login to show that in the DDL log file; it will record timestamp of only first DDL executed in a session.

Following is what you can see in the DDL log file.

Thu Jun 11 12:24:21 2015 diag_adl:create table test as select * from user_tables diag_adl:alter table test add (time_stamp date) diag_adl:create or replace trigger test_trig after insert on test declare a number; begin select count(*) into a from test; end; diag_adl:drop table test Thu Jun 11 12:27:42 2015 diag_adl:create table test as select * from user_tables Thu Jun 11 12:27:59 2015 diag_adl:drop table test

As mentioned in official document from oracle, following DDLs are recorded in DDL log file

ALTER/CREATE/DROP/TRUNCATE CLUSTER ALTER/CREATE/DROP FUNCTION ALTER/CREATE/DROP INDEX ALTER/CREATE/DROP OUTLINE ALTER/CREATE/DROP PACKAGE ALTER/CREATE/DROP PACKAGE BODY ALTER/CREATE/DROP PROCEDURE ALTER/CREATE/DROP PROFILE ALTER/CREATE/DROP SEQUENCE CREATE/DROP SYNONYM ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE ALTER/CREATE/DROP TRIGGER ALTER/CREATE/DROP TYPE ALTER/CREATE/DROP TYPE BODY DROP USER ALTER/CREATE/DROP VIEW