This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,588 Users
  • 2,269,776 Discussions
  • 7,916,825 Comments

Discussions

DDL logging

blessed DBA
blessed DBA Member Posts: 218
edited Mar 16, 2018 8:00AM in Database Ideas - Ideas

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:

  • CREATE|ALTER|DROP|TRUNCATE TABLE
  • DROP USER
  • CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
blessed DBA
1 votes

Active · Last Updated

Comments