DDL LOGGING

DDL LOGGING

There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature. 
The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.

To enable DDL logging

SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;

The following DDL statements are likely to be recorded in the xml/log file:

1. CREATE|ALTER|DROP|TRUNCATE TABLE
2. DROP USER
3. CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE.