The new 11g feature ENABLE_DDL_LOGGING initialization parameter and the new oracle11g feature

The new 11g feature ENABLE_DDL_LOGGING initialization parameter and the new oracle11g feature

ENABLE_DDL_LOGGING starts from 11g, but there are bugs in 11.2.0.2 and 11.1.0.7. This bug is described:

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

This bug is fixed in the following versions:

 

The fix for 12938609 is first supported in

12.1.0.1 (Base Release) 11.2.0.4 (Server Patch Set)

 

After this parameter is set to TRUE, the following DDL statements can be recorded in the alert Log. However, note that only the DDL execution time is recorded, does not include the information about the client where the DDL statement is executed.

•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

 

Source: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams085.htm#REFRN10302

Below is an experiment

[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> 

As you can see, this parameter is a dynamic parameter and can be modified online.

The alert Log is as follows:

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.

The alert Log is as follows:

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

That is to say, the DDL shown in the alert Log completely copies the commands executed at that time, instead of adding the user information in the execution environment at that time.