| 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@pdbdev
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@pdbdev
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@pdbdev
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@pdbdev
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. |
|
