Oracle Supplemental Log Data
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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;

Related Topics
Data Guard
GoldenGate
Pragmas

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved