Oracle | Supplemental Logging

Oracle Supplemental Log Data Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Instructs the database to add additional information into the redo logs to support replication environment transactions such as those in GoldenGate, Logical Data Guard, and Stream where the source table does not have a primary key constraint or there is other missing information necessary to unambiguously replicate the transaction on the target.

Related Data Dictionary Objects

CDB_SUPPLEMENTAL_LOGGING

DBA_SUPPLEMENTAL_LOGGING

V$DATABASE

Pragma SUPPLEMENTAL_LOG_DATA Examples

Examples PRAGMA SUPPLEMENTAL_LOG_DATA(CreateDateIndex, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(CreateNumberIndex, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(delete_table_stats, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(gather_table_stats, MANUAL); PRAGMA SUPPLEMENTAL_LOG_DATA(SyncIndex, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(compileSchema, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(deleteSchema, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(purgeSchema, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(registerURI, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(registerSchema, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(CopyEvolve, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(InPlaceEvolve, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO); PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED); PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);

conn sys@pdbdev as sysdba

      

      SELECT DISTINCT owner, name, type

      FROM dba_source

      WHERE text LIKE '%PRAGMA SUPPLEMENTAL%';

      --======================================

      conn uwclass/uwclass@pdbdev

      

      CREATE CLUSTER
      sorted_hc (

      program_id  NUMBER(3),

      line_id     NUMBER(10) SORT,

      delivery_dt DATE SORT)

      TABLESPACE uwdata

      HASHKEYS 9

      HASH IS program_id;

      

      CREATE TABLE shc_airplane (

      program_id  NUMBER(3),

      line_id     NUMBER(10) SORT,

      delivery_dt DATE SORT,

      customer_id VARCHAR2(3),

      order_dt    DATE)

      CLUSTER sorted_hc (program_id, line_id, delivery_dt);

      

      conn sys@pdbdev as sysdba

      

      -- measure amount of redo

      col module format a30

      

      SELECT sql_hash_value, value redo

      FROM gv$session s, gv$sesstat ss, gv$statname sn

      WHERE s.sid = ss.sid

      AND ss.statistic# = sn.statistic#

      AND name = 'redo size'

      AND module = 'SQL*Plus'

      AND schemaname = 'UWCLASS'

      ORDER BY redo;

      

      CREATE OR REPLACE PROCEDURE p1 IS

       pid     shc_airplane.program_id%TYPE;

       datemod NUMBER(10,5);

      BEGIN

        pid := 777;

        FOR i IN 1..999

        LOOP

          SELECT DECODE(pid, 737, 747, 747, 757,

          757, 767, 767, 777, 777, 737)

          INTO pid

          FROM dual;

      

          SELECT ROUND((EXTRACT(SECOND FROM SYSTIMESTAMP) * 1000), -2) / 100

          INTO datemod

          FROM dual;

      

          INSERT INTO shc_airplane

          (program_id, line_id, delivery_dt, order_dt)

          VALUES

          (pid, i, SYSDATE+datemod, SYSDATE-datemod);

        END LOOP;

        COMMIT;

      END;

      /

      

      -- measure redo generated

      SELECT sql_hash_value, value redo

      FROM gv$session s, gv$sesstat ss, gv$statname sn

      WHERE s.sid = ss.sid

      AND ss.statistic# = sn.statistic#

      AND name = 'redo size'

      AND module = 'SQL*Plus'

      AND schemaname = 'UWCLASS'

      ORDER BY redo;

      

      CREATE OR REPLACE PROCEDURE p2 IS

       PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

       pid     shc_airplane.program_id%TYPE;

       datemod NUMBER(10,5);

      BEGIN

        pid := 777;

        FOR i IN 1..999

        LOOP

          SELECT DECODE(pid, 737, 747, 747, 757, 757, 767, 767, 777, 777, 737)

          INTO pid

          FROM dual;

      

          SELECT ROUND((EXTRACT(SECOND FROM SYSTIMESTAMP) * 1000), -2) / 100

          INTO datemod

          FROM dual;

      

          INSERT INTO shc_airplane

          (program_id, line_id, delivery_dt, order_dt)

          VALUES

          (pid, i, SYSDATE+datemod, SYSDATE-datemod);

        END LOOP;

        COMMIT;

      END p2;

      /

      

      -- measure amount of redo

      SELECT sql_hash_value, value redo

      FROM gv$session s, gv$sesstat ss, gv$statname sn

      WHERE s.sid = ss.sid

      AND ss.statistic# = sn.statistic#

      AND name = 'redo size'

      AND module = 'SQL*Plus'

      AND schemaname = 'UWCLASS'

      ORDER BY redo;