Oracle Edition Based Redefinition Demo 4
Version 11.2.0.3
 

Edition Basics 15: Create Infrastructure
This demo is based on Demo 1 and does not depend on having run either Demo 2 or Demo 3.

The point of this demo is to handle an issue that can arise with editioning when adding new columns. We very likely will need to populate the new column with data already in the table. New data can be handled by CROSSEDITION TRIGGERS but what to do with the potentially millions/billions of rows that already exist. An option for small tables, the one we have used previously in these demos is to just perform an UPDATE. But do this on a very large table and you can potentially bring the system to its knees.

These demos will show two different ways of accomplishing the task. The first using DBMS_SQL will not solve the problem of very large tables but does demonstrate using the FORWARD CROSSEDITION trigger. The second demonstrates how, with a very large table, the update can be done in a slow and measured way.

First lets create the necessary infrastructure for the demos.
conn ebradmin/ebradmin

sho edition

TRUNCATE TABLE person_tab;

CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  dbms_output.put_line('Firing BI_PERSON in edition ' || sys_context('USERENV', 'CURRENT_EDITION_NAME'));
  IF INSERTING THEN
    :NEW.per_id := per_id_seq.NEXTVAL;
  ELSIF UPDATING THEN
    :NEW.per_id := per_id_seq.NEXTVAL+100;
  END IF;
END bi_person;
/

INSERT INTO person (full_name) VALUES ('Tom Kyte');
INSERT INTO person (full_name) VALUES ('Mogens Norgaard');
INSERT INTO person (full_name) VALUES ('Connor McDonald');
INSERT INTO person (full_name) VALUES ('Julian Dyke');
INSERT INTO person (full_name) VALUES ('Richard Foote');
COMMIT;

SELECT * FROM person;

-- create the leaf edition
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

-- create a new editioning view showing the way the table will look
CREATE OR REPLACE EDITIONING VIEW person AS
SELECT per_id, first_name, last_name, chg_date
FROM person_tab;

-- create a new trigger in the child edition actualizing the trigger
CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  dbms_output.put_line('Firing BI_PERSON in edition ' || sys_context('USERENV', 'CURRENT_EDITION_NAME'));
  IF INSERTING THEN
    :NEW.per_id := per_id_seq.NEXTVAL;
  ELSIF UPDATING THEN
    :NEW.per_id := per_id_seq.NEXTVAL+1000;
  END IF;
END bi_person;
/

SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 3,2;

INSERT INTO person (first_name, last_name, chg_date) VALUES ('Dan','Morgan', SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Hans','Forbrich',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Jonathan','Lewis',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Bryn','Llewellyn',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Cary','Millsap',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Caleb','Small',SYSDATE);
COMMIT;

SELECT * FROM person;
 
Edition Basics 16: Create Crossedition Triggers
Now lets recreate the CROSSEDITION triggers we used in Demo 3 Step 11.
conn ebradmin/ebradmin

sho edition

ALTER SESSION SET EDITION=demo_ed;

sho edition

CREATE OR REPLACE TRIGGER person_fwd_xed
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
FORWARD CROSSEDITION
DISABLE
BEGIN
  :NEW.first_name := TRIM(SUBSTR(:NEW.full_name,1,INSTR(:NEW.full_name,' ',1,1)));
  :NEW.last_name := TRIM(SUBSTR(:NEW.full_name,INSTR(:NEW.full_name,' ',1,1)));
  :NEW.chg_date := SYSDATE;
  dbms_output.put_line('Forward Crossedition Trigger Fired');
END person_fwd_xed;
/

CREATE OR REPLACE TRIGGER person_rev_xed
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
REVERSE CROSSEDITION
DISABLE
BEGIN
  :NEW.full_name := :NEW.first_name || ' ' || :NEW.last_name;
  dbms_output.put_line('Reverse Crossedition Trigger Fired');
END person_rev_xed;
/

-- enable the triggers
ALTER TRIGGER person_fwd_xed ENABLE;
ALTER TRIGGER person_rev_xed ENABLE;

SELECT trigger_name, status
FROM user_triggers;

ALTER SESSION SET EDITION=ora$base;

sho edition

set serveroutput on

SELECT * FROM person_tab;

INSERT INTO person (full_name) VALUES ('Joze Senegacnik');
-- fires forward trigger

SELECT * FROM person_tab;

-- generates an important error to learn
ALTER SESSION SET EDITION=demo_ed;

COMMIT;

ALTER SESSION SET EDITION=demo_ed;

INSERT INTO person (first_name, last_name, chg_date) VALUES ('Safra', 'Catz', SYSDATE);
-- fires reverse trigger
-- note also that the table triggers, attached to the editioning view fire before the crossedition triggers


COMMIT;

SELECT * FROM person_tab;
 
Edition Basics 17: DBMS_SQL.PARSE
DBMS_SQL.PARSE has 12 overloads of which 8 now have editioning related parameters. For this demo we will use overload 6.
conn ebradmin/ebradmin

SELECT * FROM person_tab;
 
  dbms_utility.wait_on_pending_dml(
tables  IN     VARCHAR2,
timeout IN     BINARY_INTEGER,
scn     IN OUT NUMBER)
RETURN BOOLEAN;
 

DECLARE
 out_scn NUMBER := NULL;
BEGIN
  IF NOT dbms_utility.wait_on_pending_dml('person_tab', 2, out_scn) THEN
    RAISE_APPLICATION_ERROR(-20105, 'Wait On Pending DML timed out due to transactions started before ' || out_scn);
  END IF;
END;
/

-- this can also be done with a simple DDL statement
ALTER SESSION SET ddl_lock_timeout=2;

  dbms_sql.parse(
c                          IN INTEGER,
statement                  IN VARCHAR2,
language_flag              IN INTEGER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE);
 

DECLARE
 c      NUMBER := dbms_sql.open_cursor();
 retval NUMBER;
BEGIN
  dbms_sql.parse(c, 'UPDATE person_tab SET name = name', dbms_sql.native, NULL, 'person_fwd_xed', TRUE);
  retval := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
  COMMIT;
END;
/

SELECT * FROM person_tab;
 
Edition Basics 18: DBMS_PARALLEL_EXECUTE
One critically important job, especially with very large tables, is the need to, in a slow and measured manner, populate the new columns prior to switchover. DBMS_PARALLEL_EXECUTE allows for chunking a table into a number of appropriately sized pieces and controlling the impact of the change on the system.
conn ebradmin/ebradmin

SELECT name, value
FROM v$parameter
WHERE name = 'parallel_threads_per_cpu';

SELECT dbms_parallel_execute_internal.default_parallelism
FROM dual;

  dbms_parallel_execute.create_task(
task_name IN VARCHAR2,
comment   IN VARCHAR2 DEFAULT NULL);

dbms_parallel_execute.create_chunks_by_rowid(
task_name   IN VARCHAR2,
table_owner IN VARCHAR2,
table_name  IN VARCHAR2,
by_row      IN BOOLEAN,
chunk_size  IN NUMBER);

dbms_parallel_execute.run_task(
task_name                  IN VARCHAR2,
sql_stmt                   IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE,
parallel_level             IN NUMBER   DEFAULT 0,
job_class                  IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS');

dbms_parallel_execute.task_status(task_name IN VARCHAR2) RETURN NUMBER;

dbms_parallel_execute.resume_task(
task_name IN VARCHAR2,
force     IN BOOLEAN DEFAULT FALSE);

dbms_parallel_execute.drop_task(task_name IN VARCHAR2);
 

SELECT * FROM person_tab;

DECLARE
 l_sql_stmt VARCHAR2(512);
 l_try      NUMBER := 0;
 l_status   NUMBER;
BEGIN
  dbms_parallel_execute.create_task('EBRTASK');

  dbms_parallel_execute.create_chunks_by_rowid('EBRTASK', 'EBRADMIN', 'PERSON_TAB', TRUE, 100000);

  l_sql_stmt := 'UPDATE /*+ ROWID(pt) */ ebradmin.person_tab pt
  SET pt.ebr_demo = SYSDATE
  WHERE rowid BETWEEN :start_id AND :end_id';

  dbms_parallel_execute.run_task('EBRTASK', l_sql_stmt, dbms_sql.native, parallel_level=>1);

  l_status := dbms_parallel_execute.task_status('EBRTASK');
  WHILE(l_try < 2 and l_status != dbms_parallel_execute.finished) LOOP
    l_try := l_try + 1;
    dbms_parallel_execute.resume_task('EBRTASK');
    l_status := dbms_parallel_execute.task_status('EBRTASK');
  END LOOP;

  dbms_parallel_execute.drop_task('EBRTASK');
END;
/

SELECT * FROM person_tab;
This concludes Demo 4. When I come up with new functionality it will appear in Demo 5.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved