How to Enable or Disable the Supplemental Logging in Oracle Database

_____________________________________________________________________________________________________________________

Steps to Enable the supplemental logging

SQL> set timing on time on

08:42:21 SQL> select

   SUPPLEMENTAL_LOG_DATA_MIN,

   SUPPLEMENTAL_LOG_DATA_PK,

   SUPPLEMENTAL_LOG_DATA_UI

from

   v$database;

SUPPLEME SUP SUP

-------- --- ---

NO       NO  NO

08:42:33 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Database altered.

08:43:09 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Database altered.

08:43:29 SQL> ALTER DATABASE add SUPPLEMENTAL LOG DATA;

Database altered.

Elapsed: 00:00:00.11

08:43:56 SQL> select

   SUPPLEMENTAL_LOG_DATA_MIN,

   SUPPLEMENTAL_LOG_DATA_PK,

   SUPPLEMENTAL_LOG_DATA_UI

from

   v$database;

SUPPLEME SUP SUP

-------- --- ---

YES      YES YES

Steps to Disable the supplemental logging

SQL> select

   SUPPLEMENTAL_LOG_DATA_MIN,

   SUPPLEMENTAL_LOG_DATA_PK,

   SUPPLEMENTAL_LOG_DATA_UI

from

   v$database;

SUPPLEME SUP SUP

-------- --- ---

YES      YES YES

08:41:43 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Database altered.

08:41:59 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Database altered.

08:42:11 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Database altered.

08:42:21 SQL> select

   SUPPLEMENTAL_LOG_DATA_MIN,

   SUPPLEMENTAL_LOG_DATA_PK,

   SUPPLEMENTAL_LOG_DATA_UI

from

   v$database;

SUPPLEME SUP SUP

-------- --- ---

NO       NO  NO

_____________________________________________________________________________________________________________________