Enable or disable the supplemental logging in Oracle

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.