12c DDL logging

oracle在12c中引入了ddl logging的功能
此功能有初始化参数enable_ddl_logging控制，默认为FALSE，设置为TRUE后，oracle会将执行的ddl语句记录到一个log日志里面

我们来测试一下
SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
--默认没有开启
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

SQL> create table goolen as select * from dba_objects;
Table created.
SQL> create index ind_oid on goolen(object_id);
Index created.
SQL> alter table goolen modify(object_id not null);
Table altered.
SQL> truncate table goolen;
Table truncated.
SQL> drop table goolen purge;
Table dropped.
$ adrci
ADRCI: Release 12.1.0.1.0 - Production on Sun Oct 27 13:34:55 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/opt/app/oracle"
adrci> show log
Choose the home from which to view diagnostic logs:
1: diag/tnslsnr/release-mysql/listener
2: diag/rdbms/ora12c/ora12c
Q: to quit

$ cd /opt/app/oracle/diag/rdbms/ora12c/ora12c/log
$ ls
ddl  ddl_ora12c.log  debug  test

$ cat ddl_ora12c.log 
diag_adl:create table goolen as select * from dba_objects
diag_adl:create index ind_oid on goolen(object_id)
diag_adl:alter table goolen modify(object_id not null)
diag_adl:truncate table goolen
diag_adl:drop table goolen purge

$ cat ddl/log.xml 
<msg time='2013-10-27T13:33:12.652+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='release' host_addr='192.168.100.92'
 version='1'>
 <txt>create table goolen as select * from dba_objects
 </txt>
</msg>
<msg time='2013-10-27T13:33:29.758+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='release' host_addr='192.168.100.92'>
 <txt>create index ind_oid on goolen(object_id)
 </txt>
</msg>
<msg time='2013-10-27T13:33:47.515+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='release' host_addr='192.168.100.92'>
 <txt>alter table goolen modify(object_id not null)
 </txt>
</msg>
<msg time='2013-10-27T13:34:35.638+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='release' host_addr='192.168.100.92'>
 <txt>truncate table goolen
 </txt>
</msg>
<msg time='2013-10-27T13:34:41.261+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='release' host_addr='192.168.100.92'>
 <txt>drop table goolen purge
 </txt>

</msg>