This  ENABLE_DDL_LOGGING parameter has been introduced in Oracle 12c.

If this ENABLE_DDL_LOGGING is enabled, then DDL records are written to the ADR.
All DDL operations like alter/create/drop/truncate objects. Only drop user will be logged, But create user will not be.

Enable the parameter:

SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean FALSE

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

SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean TRUE

Do some DDL operations:

SQL> create user raj identified by raj;
User created.

SQL> create table raj.t8 (n number);
Table created.

SQL> drop user raj cascade;
User dropped.

 

Check the log:

 

cd /u02/app/oracle/diag/rdbms/cdborcl/cdborcl/log/ddl
[oracle@localhost ddl]$ more log.xml <msg time=’2014-07-10T01:52:00.323-04:00′ org_id=’oracle’ comp_id=’rdbms’  msg_id=’kpdbLogDDL:15115:2946163730′ type=’UNKNOWN’ group=’diag_adl’ level=’16’ host_id=’usa0300lx574′ host_addr=’13.129.131.42′ version=’1′>
<txt>create table raj.t8 (n number) </txt> </msg> <msg time=’2014-07-10T01:53:18.329-04:00′ org_id=’oracle’ comp_id=’rdbms’ 36  msg_id=’kpdbLogDDL:15115:2946163730′ type=’UNKNOWN’ group=’diag_adl’ level=’16’ host_id=’usa0300lx574′ host_addr=’13.129.131.42′> <txt>drop user raj cascade </txt> </msg>
Here we can see All ddl stmts are logged,  Except the create user command.

About The Author

Comments

Leave a Reply