CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Pragma SUPPLEMENTAL_LOG_DATA
Version 11.1
 
General
Related Data Dictionary Objects
source$ dba_source all_source user_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);
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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [219 users online]    © 2010 psoug.org