Database Level Logging

When using the identification key logging method, the database can be instructed to log all the columns in primary key, foreign key, and unique constraints. This would be applicable for the tables in the source database. This method is followed when the intention is to capture changes for the entire database.

The following SQL statement sets up the database level logging using the identification key log method:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
     (primary key, unique, foreign key) COLUMNS ;

There is also an available option for specifying a particular type of attribute or ALL. These concepts are explained next.

• ALL - This option specifies that when a row is updated, all columns of that row, except LOBs, LONGS, and ADTs, are placed in the redo log file. To enable ALL column logging at the database level, execute the following:

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

• PRIMARY KEY - This option causes the database to place all columns of a row’s primary key in the redo log file whenever a row containing a primary key is updated, even if no value in the primary key has changed. To enable PRIMARY KEY logging at the database level, execute the following:

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