Report changes with ENABLE_DDL_LOGGING=TRUE

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE. 

When set to TRUE, the database reports schema changes in real time into the DDL alert log.

SQL> show con_id

CON_ID

------------------------------

1

SQL> show parameter enable_ddl_logging

NAME         TYPE  VALUE

------------------------------------ ----------- ------------------------------

enable_ddl_logging       boolean  FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=memory;

System altered.

SQL> show parameter enable_ddl_logging;

NAME         TYPE  VALUE

------------------------------------ ----------- ------------------------------

enable_ddl_logging       boolean  TRUE

The alert log show the change as expected:

Thu Sep 26 15:39:47 2013

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

SQL> alter session set container=pdb1;    

Session altered.

SQL> create user newuser identified by newuser;

User created.

SQL> grant connect, resource, create table, unlimited tablespace to newuser;

Grant succeeded.

SQL> create table newuser.table1 (col1 number(9));

Table created.

SQL> alter table newuser.table1 add col2 varchar2(10);

Table altered.

SQL> drop user newuser cascade;

User dropped.

SQL> create user abc identified by abc;

User created.

SQL> drop user abc cascade;

User dropped.

[oracle@magenta log]$ pwd

/u01/app/oracle/diag/rdbms/orcl12c/orcl12c/log

[oracle@magenta log]$ ll

total 16

drwxr-x---. 2 oracle oinstall 4096 Sep 26 15:46 ddl

-rw-r-----. 1 oracle oinstall  203 Sep 26 15:51 ddl_orcl12c.log

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 debug

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 test

[oracle@magenta log]$ tail -f ddl_orcl12c.log 

Thu Sep 26 15:46:13 2013

diag_adl:create table newuser.table1 (col1 number(9))

diag_adl:alter table newuser.table1 add col2 varchar2(10)

diag_adl:drop user newuser cascade

diag_adl:drop user abc cascade

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the DDL alert log:

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