有關11g新特性ENABLE_DDL_LOGGING初始化參數

有關11g新特性ENABLE_DDL_LOGGING初始化參數

ENABLE_DDL_LOGGING從11g開始就有了，只不過在11.2.0.2和11.1.0.7有bug，該bug被描述為：

Bug 12938609 - ENABLE_DDL_LOGGING does not log RENAME table statements (文檔 ID 12938609.8)

該bug在如下版本中被fix：

12.1.0.1 (Base Release)11.2.0.4 (Server Patch Set)

該參數設置為TRUE後，可以在alert日志中記錄如下DDL語句，但是請注意，僅僅記錄的是DDL執行的時間，不包括DDL語句在哪個客戶端執行的等等信息


?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

如上來源：http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams085.htm#REFRN10302

下面做實驗

[oracle@rhel63single ~]$ sqlplus lc0029999/aaaaaa

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 00:11:32 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> show parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE
SQL> show user
USER is "LC0029999"
SQL> create table t1 as select * from dba_objects where 1=2; 

Table created.

SQL> alter table t1 rename to t11;

Table altered.

SQL> 

可以看到，該參數是動態參數，可以在線修改。

此時的alert日志如下：

Wed Jun 10 01:38:35 2015
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Wed Jun 10 01:39:17 2015
create table t1 as select * from dba_objects where 1=2
Wed Jun 10 01:40:03 2015
alter table t1 rename to t11

SQL> create table lc0039999.t1 as select * from dba_objects where 1=2;

Table created.

此時的alert日志如下：

Wed Jun 10 01:46:52 2015
create table lc0039999.t1 as select * from dba_objects where 1=2

也就是說，alert日志中顯示的DDL是完全照搬當時執行的命令，而不會把當時執行環境中的user信息給添加上。