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>
此功能有初始化参数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>