Oracle | Pragma Supplemental Logging

Oracle Pragma Supplemental Log Data Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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.

Data Dictionary Objects

ALL_SOURCE	DBA_SUPPLEMENTAL_LOGGING	USER_SOURCE
CDB_SOURCE	DBA_SOURCE	V$DATABASE
CDB_SUPPLEMENTAL_LOGGING	SOURCE$

Supplemental Logging SELECT column_name FROM dba_tab_cols WHERE table_name = 'V_$DATABASE' AND column_name LIKE '%SUPP%' ORDER BY 1; COLUMN_NAME ------------------------------ SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_PL SUPPLEMENTAL_LOG_DATA_SR SUPPLEMENTAL_LOG_DATA_UI

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;