Database Level Logging

• UNIQUE index - This option causes the database to place all columns of a row’s composite unique index key or bitmap index in the redo log file if any column belonging to the composite unique index key or bitmap index is modified. To enable UNIQUE index key and bitmap index logging at the database level, execute:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

• FOREIGN KEY - This option causes the database to place all columns of a row’s foreign key in the redo log file if any column belonging to the foreign key is modified. To enable FOREIGN KEY logging at the database level, execute the following:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

In many situations, database-wide supplemental logging may not be desirable as it would produce huge volumes of additional columns of data into the redo logs. Unless the goal is to set up a database-wide capture mechanism, this approach would not be the most desirable. Setting up supplemental logging at the table level is a controlled operation. By this method, better control can be exercised on the selection of tables and columns to be logged additionally.

The next sections provide table level logging examples.

Table-Level Identification Key Logging

When logging is specified at the table level, only the table level logging takes place. Identification key logging at the table level offers the same options as those provided at the database level: ALL, PRIMARY KEY, FOREIGN KEY, and UNIQUE index key. By specifying the right option, the logging level can be controlled.