Check Supplemental logging at database or table level

Supplemental logging is generate or add extra logging information for the change data capture process which is used for capturing the changes like insert/update/delete operation with unique identified a row on target database. So that it will easy to find at source database. Basic it enable for replication services like Golden Gate.

Example when the column of row is updated at the source database then it need to identified the row at target so addition row number or unique identifier for that row is attached with data for updated the target database.

Two types of Supplemental logging : Database or Table Level

Check the Supplemental logging enabled at Database level

select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

Enabling Supplemental Logging at Database Level

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
OR
SQL>alter database add supplemental log data (all) columns;

Disable the Supplemental Logging at Database Level

Note: For disable the supplemental logging at database level. You need to follow the following sequence order:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Check the Supplemental logging at Table Level

select count(*) from ALL_LOG_GROUPS where LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER= 'HR' and TABLE_NAME='EMPLOYEES';

Enabling Supplemental Logging at Table Level

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
OR 
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
OR  
ALTER TABLE HR.EMPLOYEES 
ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Disable the supplemental Logging at table level

ALTER TABLE HR.EMPLOYEES DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE HR.EMPLOYEES DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE HR.EMPLOYEES DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;