Mining Your Own Business, Is Supplemental Logging Enabled?

January 12, 2010

A couple years ago the following question was asked in the comp.databases.oracle.server Usernet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/7d4bfb37a4fd4a33/

How can I determine if SUPPLEMENTAL LOGGING as been enable at the database level?

Done by this command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

The original poster did not mention why he wanted to check the status of supplemental logging, but I suspect that he wanted to use LogMiner.

The response that I provided to the OP follows:

Take a look at the output of the following:

SELECT
  SUPPLEMENTAL_LOG_DATA_MIN,
  SUPPLEMENTAL_LOG_DATA_PK,
  SUPPLEMENTAL_LOG_DATA_UI
FROM
  V$DATABASE;

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

Now, execute your statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Then check again: 

SELECT
  SUPPLEMENTAL_LOG_DATA_MIN,
  SUPPLEMENTAL_LOG_DATA_PK,
  SUPPLEMENTAL_LOG_DATA_UI
FROM
  V$DATABASE;

SUPPLEME SUP SUP
-------- --- ---
IMPLICIT YES YES

From the Oracle Database Reference 10g Release 2 pg 6-54 (PDF page 670):
“SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables:

•  NO – None of the database-wide supplemental logging directives are enabled 
• IMPLICIT – Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled
• YES – Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement

See Also: Oracle Database SQL Reference for additional information about the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement

SUPPLEMENTAL_LOG_DATA_PK
VARCHAR2(3) For all tables with a primary key, indicates whether all columns of the primary key are placed into the redo log whenever an update is performed (YES) or not (NO)
See Also: Oracle Database SQL Reference for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement

SUPPLEMENTAL_LOG_DATA_UI
VARCHAR2(3) For all tables with a unique key, indicates whether all other columns belonging to the unique key are placed into the redo log if any of the unique key columns are modified (YES) or not (NO) See Also: Oracle Database SQL Reference for more information about the
ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement”