Oracle Pragma SUPPLEMENTAL_LOG_DATA
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Data Dictionary Objects
ALL_SOURCE DBA_SOURCE USER_SOURCE
CDB_SOURCE SOURCE$  
 
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 / as sysdba

SELECT DISTINCT owner, name, type
FROM dba_source
WHERE text LIKE '%PRAGMA SUPPLEMENTAL%';
--======================================
conn uwclass/uwclass

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 / 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
Pragmas
Pragma Autonomous Transaction
Pragma Exception Init
Pragma Inline
Pragma Serially Reusable

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