Enabling DDL Logging- Tables and Constraints

Oracle allows you to enable the logging of DDL statements to a log file. This type of logging is switched on with the ENABLE_DDL_LOGGING parameter (the default is FALSE). You can set this at the session or system level. This feature provides you with an audit trail regarding which DDL statements have been issued and when they were run. Here is an example of setting this parameter at the system level:

SQL> alter system set enable_ddl_logging=true scope=both;

The file is an alert log type file, and depending on the capture, there can be multiple files, but it is a different file just for capturing the DDL statements. The location of the file depends on the database version, but you can query the location path:

SQL> select value from v$diag_info where name=’Diag Alert’; SQL> select value from v$diag_info where name=’ADR Home’;

Within this directory, there will be a file with the format ddl_<SID>.log. This file contains a log of DDL statements that have been issued after DDL logging has been enabled, and DDL logging is found in the log.xml file.

Modifying a Table

Altering a table is a common task. New requirements frequently mean you need to rename, add, drop, or change column data types. In development environments, changing a table can be a trivial task: you do not often have large quantities of data or hundreds of users simultaneously accessing a table. However, for active production systems, you need to understand the ramifications of trying to change tables that are currently being accessed or that are already populated with data or both.

Obtaining the Needed Lock

When you modify a table, you must have an exclusive lock on the table. One issue is that if a DML transaction has a lock on the table, you cannot alter it. In this situation, you receive this error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The prior error message is somewhat confusing, leading you to believe that you can resolve the problem by acquiring a lock with NOWAIT. However, this is a generic message that is generated when the DDL you are issuing cannot obtain an exclusive lock on the table. Instead of scheduling an outage for a maintenance window or trying over and over

again with the statement, you can use the DDL_LOCK_TIMEOUT parameter.

Setting the DDL_LOCK_TIMEOUT parameter will repeatedly attempt to run a DDL statement until it obtains the required lock on the table. This can be set at the system or

session level, and the time is in seconds:

SQL> alter session set ddl_lock_timeout=100;

Another way to avoid waiting on transactions and perform modifications to the table is to use the DBMS_REDEFINITION package. This package is for online table operations and allows for table changes from the column types, name, and size of renaming tables.

Using this package will allow for online operations without disrupting the database users for implementing other options. This is also a good way to validate new procedures and table changes before making the switch.