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_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='release' host_addr='192.168.100.92'
version='1'>
create table goolen as select * from dba_objects

msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='release' host_addr='192.168.100.92'>
create index ind_oid on goolen(object_id)

msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='release' host_addr='192.168.100.92'>
alter table goolen modify(object_id not null)

msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='release' host_addr='192.168.100.92'>
truncate table goolen

msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='release' host_addr='192.168.100.92'>
drop table goolen purge

ITPub博客

12c DDL logging

最新文章