Thursday, December 10, 2009

11g new feature: enable_ddl_logging parameter

Parameter description:


SQL> select value,isdefault,isses_modifiable,
2 issys_modifiable,isbasic,description
3 from v$parameter
4 where name='enable_ddl_logging'
5 /

VALUE ISDEFAULT ISSES ISSYS_MOD ISBAS DESCRIPTION
---------- --------- ----- --------- ----- --------------------
FALSE TRUE TRUE IMMEDIATE FALSE enable ddl logging


Input from SQL*Plus:


SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> conn donghua/donghua
Connected.
SQL> create table t1 as select * from dual;

Table created.

SQL> alter table t1 add (id number,name varchar2(20));

Table altered.

SQL> grant select on t1 to public;

Grant succeeded.

SQL> alter table t1 read only;

Table altered.

SQL> drop table t1;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.


Output from alert log


ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
2009-12-10 21:50:50.562000 +08:00
create table t1 as select * from dual
2009-12-10 21:51:13.062000 +08:00
alter table t1 add (id number,name varchar2(20))
2009-12-10 21:51:40.062000 +08:00
Starting background process CJQ0
CJQ0 started with pid=21, OS id=4896
2009-12-10 21:51:43.078000 +08:00
alter table t1 read only
2009-12-10 21:51:55.359000 +08:00
drop table t1