Oracle SUPPLEMENTAL_LOG


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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