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

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

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：

 

The fix for 12938609 is first included in

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/aaaaaaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 00:11:32 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter ddlNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------ddl_lock_timeout                     integer     0enable_ddl_logging                   boolean     FALSESQL> alter system set enable_ddl_logging=true;System altered.SQL> show parameter ddlNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------ddl_lock_timeout                     integer     0enable_ddl_logging                   boolean     TRUESQL> show userUSER 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 2015ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;Wed Jun 10 01:39:17 2015create table t1 as select * from dba_objects where 1=2Wed Jun 10 01:40:03 2015alter 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 2015create table lc0039999.t1 as select * from dba_objects where 1=2

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