有关11g新特性ENABLE_DDL_LOGGING 初始化参数

最新推荐文章于 2023-09-22 22:17:29 发布

原创最新推荐文章于 2023-09-22 22:17:29 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

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

该参数设置为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信息给添加上。