| Editioning Automation 1: Preparation |
This demo is my answer to the comments I often hear about the tremendous amount of work required to prepare an application schema for EBR.
I've no doubt that this will prove a bit simplistic, for example it does not handle other than by outputting to the screen, tables with 29 to 30 character names.
But it will likely convert 98+% of all of the schemas I've seen in years.
Let's start off by doing the appropriate preparations as the DBA. |
conn sys@pdbdev as sysdba
SELECT * FROM dba_editions;
-- create a child edition if one does not already exist
CREATE EDITION demo_ed; |
| |
| Editioning Automation 2: Create Editioning Views |
| This demo will show how easily one can take a production schema, we will requisition Oracle's sample HR demo, and make it ready for EBR. |
conn hr/hr@pdbdev
SELECT table_name, table_type
FROM user_all_tables;
SELECT table_name, trigger_name
FROM user_triggers;
SELECT view_name
FROM user_editioning_views;
set serveroutput on
DECLARE
CURSOR tcur IS
SELECT uat.table_name
FROM user_all_tables uat
WHERE uat.table_type IS NULL;
NewTableName user_all_tables.table_name%TYPE;
NewViewName user_views.view_name%TYPE;
TriggerDDL CLOB;
comp_error EXCEPTION;
PRAGMA EXCEPTION_INIT(comp_error, -24344);
BEGIN
FOR trec IN tcur LOOP
IF LENGTH(trec.table_name) < 29 THEN
NewViewName := trec.table_name;
NewTableName := trec.table_name || '_T';
EXECUTE IMMEDIATE 'RENAME ' || trec.table_name || ' TO ' || NewTableName;
EXECUTE IMMEDIATE 'CREATE EDITIONING VIEW ' || NewViewName || ' AS SELECT * FROM '|| NewTableName;
DECLARE
CURSOR rcur IS
SELECT trigger_name FROM user_triggers
WHERE table_name = NewTableName;
BEGIN
FOR rrec IN rcur LOOP
triggerDDL := dbms_metadata.get_ddl('TRIGGER', rrec.trigger_name);
triggerDDL := REPLACE(triggerDDL, NewTableName, NewViewName);
triggerDDL := SUBSTR(triggerDDL,1,INSTR(triggerDDL,'ALTER',1,1)-1);
EXECUTE IMMEDIATE 'DROP TRIGGER ' || rrec.trigger_name;
BEGIN
EXECUTE IMMEDIATE triggerDDL;
EXCEPTION
WHEN comp_error THEN NULL;
END;
END LOOP;
END;
ELSE
dbms_output.put_line('Unable To Rename Table ' || trec.table_name);
END IF;
END LOOP;
END;
/
SELECT table_name, table_type
FROM user_all_tables;
SELECT table_name, trigger_name
FROM user_triggers;
SELECT view_name
FROM user_editioning_views;
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
-- invalid objects recompile them
conn sys@pdbdev as sysdba
exec utl_recomp.recomp_parallel(2, 'HR');
conn hr/hr@pdbdev
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
-- create a synonym
CREATE SYNONYM openworld FOR employees; |
| |
| Editioning Automation 3 |
| Taking that initial outage and getting ready for editioning, above, was simple.
Now lets leverage our infrastructure by creating and edition and watching how Oracle automatically performs the next step which is to make copies,
by pointer, of all editionable objects |
-- this will produce an exception
ALTER SESSION SET EDITION = demo_ed;
conn sys@pdbdev as sysdba
-- enable editions for the application owner and user
col editions_enabled format a20
SELECT editions_enabled
FROM dba_users
WHERE username = 'HR';
ALTER USER hr ENABLE EDITIONS;
SELECT editions_enabled
FROM dba_users
WHERE username = 'HR';
-- grant use on the edition to the application owner
SELECT grantee
FROM dba_tab_privs
WHERE privilege = 'USE';
GRANT use ON EDITION demo_ed TO hr;
SELECT grantee
FROM dba_tab_privs
WHERE privilege = 'USE';
conn hr/hr@pdbdev
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
ALTER SESSION SET EDITION=demo_ed;
SELECT sys_context('USERENV',
'CURRENT_EDITION_NAME') FROM dual;
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type
ORDER BY 1;
SELECT edition_name, object_type, COUNT(*)
FROM user_objects_ae
GROUP BY edition_name, object_type
ORDER BY 1,2;
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'TRIGGER', 'VIEW')
ORDER BY 2,1;
-- actualize a PL/SQL object
ALTER PROCEDURE add_job_history COMPILE;
SELECT edition_name, object_type, COUNT(*)
FROM user_objects_ae
GROUP BY edition_name, object_type
ORDER BY 1,2;
-- actualize remaining PL/SQL object
ALTER PROCEDURE secure_dml COMPILE;
ALTER SYNONYM openworld COMPILE;
ALTER TRIGGER secure_employees COMPILE;
ALTER TRIGGER update_job_history COMPILE;
ALTER VIEW countries COMPILE;
ALTER VIEW departments COMPILE;
ALTER VIEW employees COMPILE;
ALTER VIEW jobs COMPILE;
ALTER VIEW job_history COMPILE;
ALTER VIEW locations COMPILE;
ALTER VIEW regions COMPILE;
SELECT edition_name, object_type, COUNT(*)
FROM user_objects_ae
GROUP BY edition_name, object_type
ORDER BY 1,2;
-- which view has not been actualized
SELECT view_name
FROM user_views
MINUS
SELECT view_name
FROM user_editioning_views; |
| |
| Editioning Automation 4 |
| Now we have two changes to the HR application in version 2. We are going to add a new column to the employees table and alter the view emp_details_view. |
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
-- add get_mgr_name function as part of v2
CREATE OR REPLACE FUNCTION get_mgr(mgrid IN employees.employee_id%TYPE) RETURN VARCHAR2 AUTHID DEFINER IS
mname VARCHAR2(46);
BEGIN
SELECT first_name || ' ' || last_name
INTO mname
FROM employees
WHERE employee_id = mgrid
AND EXISTS (
SELECT manager_id
FROM departments d
WHERE d.manager_id = mgrid);
RETURN mname;
END get_mgr;
/
-- a column to employees
ALTER TABLE employees_t ADD (term_date DATE);
desc employees_t
-- recompile the editioning view
CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id emp_id, first_name, last_name, email, phone_number, hire_date, term_date,
department_id dept_id, manager_id mgr_id
FROM employees_t;
desc employees
ALTER SESSION SET EDITION=ora$base;
desc employees |
| |
| Editioning Automation 5: Undo Demo Changes |
| The following code undoes the changes to the Oracle HR demo schema and returns it to its original structure. The trigger code could potentially make this far more problematic but then, as Tom Kyte has written, "triggers are evil." So for those triggers more difficult to convert ... there is always just the matter of making a slightly more sophisticated change either in code or to the DDL source code. |
SELECT table_name, table_type
FROM user_all_tables;
SELECT table_name, trigger_name
FROM user_triggers;
SELECT view_name
FROM user_editioning_views;
DECLARE
CURSOR evcur IS
SELECT view_name, table_name
FROM user_editioning_views;
TriggerDDL CLOB;
BEGIN
FOR evrec IN evcur LOOP
DECLARE
CURSOR trcur IS
SELECT trigger_name
FROM user_triggers
WHERE table_name = evrec.view_name;
BEGIN
FOR trrec IN trcur LOOP
triggerDDL := dbms_metadata.get_ddl('TRIGGER', trrec.trigger_name);
triggerDDL := REPLACE(triggerDDL, 'ON ' || evrec.view_name, 'ON ' || evrec.table_name);
triggerDDL := SUBSTR(triggerDDL,1,INSTR(triggerDDL,'ALTER',1,1)-1);
EXECUTE IMMEDIATE 'DROP TRIGGER ' || trrec.trigger_name;
EXECUTE IMMEDIATE triggerDDL;
END LOOP;
END;
EXECUTE IMMEDIATE 'DROP VIEW ' || evrec.view_name;
EXECUTE IMMEDIATE 'RENAME ' || evrec.table_name || ' TO ' || evrec.view_name;
END LOOP;
END;
/
SELECT table_name, table_type
FROM user_all_tables;
SELECT table_name, trigger_name
FROM user_triggers;
SELECT view_name
FROM user_editioning_views;
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
-- invalid objects recompile them
conn sys@pdbdev as sysdba
exec utl_recomp.recomp_parallel(2, 'HR');
conn hr/hr@pdbdev
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
ALTER TABLE employees DROP COLUMN term_date;
conn sys@pdbdev as sysdba
REVOKE use ON EDTION demo_ed FROM hr; |
| I hope this small amount of code, written quickly in the Sofitel Hotel the Thursday before OpenWorld
demonstrates the ease of preparing an application schema for EBR. |
|
