Re: Determine if database SUPPLEMENTAL LOGGING as been enable.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 13 Aug 2007 18:35:40 -0700
Message-ID: <1187055340.788637.105940@l70g2000hse.googlegroups.com>

On Aug 13, 8:08 pm, Michael42 <melliot..._at_yahoo.com> wrote:
> 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

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. Received on Mon Aug 13 2007 - 20:35:40 CDT