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

advertisements

 

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

_____________________________________________________________________________________________________________________

Website Stats