Oracle Edition Based Redefinition Demo 6
Version 11.2.0.3
 
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 / 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

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 / as sysdba

exec utl_recomp.recomp_parallel(2, 'HR');

conn hr/hr

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 / 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

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 / as sysdba

exec utl_recomp.recomp_parallel(2, 'HR');

conn hr/hr

SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';

ALTER TABLE employees DROP COLUMN term_date;


conn / 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.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved