Determine if database SUPPLEMENTAL LOGGING as been enable.

[Michael42]

Hello,

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;

Thanks,

Michael

[Charles Hooper]

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;

SELECT
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
FROM
V$DATABASE;

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

>From 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"

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Michael42]

Charles,

Thanks very much. That is perfect.

Michael