VIEW: SYS.DBA_SUPPLEMENTAL_LOGGING

Source

View Text - Preformatted

select 'NO' MINIMAL,
       'NO' PRIMARY_KEY,
       'NO' UNIQUE_INDEX,
       'NO' FOREIGN_KEY,
       'NO' ALL_COLUMN,
       'NO' PROCEDURAL
from v$database
where SUPPLEMENTAL_LOG_DATA_MIN = 'NO'
union
-- If there is no prop$ entry, go by what v$database says.
select  'YES' MINIMAL,
        SUPPLEMENTAL_LOG_DATA_PK PRIMARY_KEY,
        SUPPLEMENTAL_LOG_DATA_UI UNIQUE_INDEX,
        SUPPLEMENTAL_LOG_DATA_FK FOREIGN_KEY,
        SUPPLEMENTAL_LOG_DATA_ALL ALL_COLUMN,
        SUPPLEMENTAL_LOG_DATA_PL  PROCEDURAL
from v$database a where
     a.SUPPLEMENTAL_LOG_DATA_MIN != 'NO' and
      not exists (select 1 from sys.props$
                  where name = 'SUPPLEMENTAL_LOG_DATA')
union
-- When DB wide minimal is enabled and the props$ entry for
-- supplemental logging is non-zero (has some relevance)
-- look at both v$database and the props$ entry to detremine
-- the effective state of supplemental logging for the PDB
-- in question.
select 'YES' MINIMAL,
       (case when (a.SUPPLEMENTAL_LOG_DATA_PK = 'NO') and
                   ((bitand(to_number(b.value$), 1) = 0))
                    then 'NO' else 'YES' end) PRIMARY_KEY,
       (case when (a.SUPPLEMENTAL_LOG_DATA_UI = 'NO') and
                   (bitand(to_number(b.value$), 2) = 0)
                    then 'NO' else 'YES' end) UNIQUE_INDEX,
       (case when (a.SUPPLEMENTAL_LOG_DATA_FK = 'NO') and
                   (bitand(to_number(b.value$), 4) = 0)
                    then 'NO' else 'YES' end) FOREIGN_KEY,
       (case when (a.SUPPLEMENTAL_LOG_DATA_ALL = 'NO') and
                   (bitand(to_number(b.value$), 32) = 0)
                    then 'NO' else 'YES' end) ALL_COLUMN,
       (case when (a.SUPPLEMENTAL_LOG_DATA_PL = 'NO') and
                   (bitand(to_number(b.value$), 64) = 0)
                    then 'NO' else 'YES' end) PROCEDURAL
from v$database a, (select value$
                    from sys.props$ where name = 'SUPPLEMENTAL_LOG_DATA') b
where a.SUPPLEMENTAL_LOG_DATA_MIN != 'NO'

View Text - HTML Formatted

SELECT 'NO' MINIMAL
, 'NO' PRIMARY_KEY
, 'NO' UNIQUE_INDEX
, 'NO' FOREIGN_KEY
, 'NO' ALL_COLUMN
, 'NO' PROCEDURAL FROM V$DATABASE WHERE SUPPLEMENTAL_LOG_DATA_MIN = 'NO' UNION -- IF THERE IS NO PROP$ ENTRY
, GO BY WHAT V$DATABASE SAYS. SELECT 'YES' MINIMAL
, SUPPLEMENTAL_LOG_DATA_PK PRIMARY_KEY
, SUPPLEMENTAL_LOG_DATA_UI UNIQUE_INDEX
, SUPPLEMENTAL_LOG_DATA_FK FOREIGN_KEY
, SUPPLEMENTAL_LOG_DATA_ALL ALL_COLUMN
, SUPPLEMENTAL_LOG_DATA_PL PROCEDURAL FROM V$DATABASE A WHERE A.SUPPLEMENTAL_LOG_DATA_MIN != 'NO' AND NOT EXISTS (SELECT 1
FROM SYS.PROPS$
WHERE NAME = 'SUPPLEMENTAL_LOG_DATA') UNION -- WHEN DB WIDE MINIMAL IS ENABLED
AND THE PROPS$ ENTRY FOR -- SUPPLEMENTAL LOGGING IS NON-ZERO (HAS SOME RELEVANCE) -- LOOK AT BOTH V$DATABASE
AND THE PROPS$ ENTRY TO DETREMINE -- THE EFFECTIVE STATE OF SUPPLEMENTAL LOGGING FOR THE PDB -- IN QUESTION. SELECT 'YES' MINIMAL
, (CASE WHEN (A.SUPPLEMENTAL_LOG_DATA_PK = 'NO') AND ((BITAND(TO_NUMBER(B.VALUE$)
, 1) = 0)) THEN 'NO' ELSE 'YES' END) PRIMARY_KEY
, (CASE WHEN (A.SUPPLEMENTAL_LOG_DATA_UI = 'NO') AND (BITAND(TO_NUMBER(B.VALUE$)
, 2) = 0) THEN 'NO' ELSE 'YES' END) UNIQUE_INDEX
, (CASE WHEN (A.SUPPLEMENTAL_LOG_DATA_FK = 'NO') AND (BITAND(TO_NUMBER(B.VALUE$)
, 4) = 0) THEN 'NO' ELSE 'YES' END) FOREIGN_KEY
, (CASE WHEN (A.SUPPLEMENTAL_LOG_DATA_ALL = 'NO') AND (BITAND(TO_NUMBER(B.VALUE$)
, 32) = 0) THEN 'NO' ELSE 'YES' END) ALL_COLUMN
, (CASE WHEN (A.SUPPLEMENTAL_LOG_DATA_PL = 'NO') AND (BITAND(TO_NUMBER(B.VALUE$)
, 64) = 0) THEN 'NO' ELSE 'YES' END) PROCEDURAL FROM V$DATABASE A
, (SELECT VALUE$
FROM SYS.PROPS$
WHERE NAME = 'SUPPLEMENTAL_LOG_DATA') B WHERE A.SUPPLEMENTAL_LOG_DATA_MIN != 'NO'