Oracle Edition Based Redefinition Demo 1
Version 11.2.0.3
 
Setup As SYS - Create Application Owner
The following statements, executed from an account with appropriate credentials is used to create the application schema owner, EBRADMIN, wit appropriate permissions for the demonstrations on this page. While this demo uses SYS, the user most likely to be used by students, it is recommended that a properly privileged DBA management account be used in any organization wishing to implement this technology. Privileges granted here for demonstration purposes, similarly, should not be granted in a non-training environment.

The demonstrations on this page are not intended to be fully inclusive of all available functionality: Student need to read Byrn's White Paper and review the Oracle docs to gain a comprehensive understanding. These demos are intended to illustrate specific aspects of value in gaining an understanding of this valuable technology.

Critically important in 12cR1 and above ... editioning is not supported for COMMON USERS (ORA-65074).
conn / as sysdba

-- view available tablespaces
SELECT tablespace_name, contents
FROM dba_tablespaces
ORDER BY 2,1;

SELECT file_name
FROM dba_data_files;

-- if a suitable tablespace does not exist then create a demo tablespace
CREATE TABLESPACE uwdata
DATAFILE 'c:\oracle\product\oradata\orabase\uwdata01.dbf' SIZE 100M
LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
DEFAULT NOCOMPRESS
SEGMENT SPACE MANAGEMENT AUTO;

-- create the application owner
CREATE USER ebradmin
IDENTIFIED BY ebradmin
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 20M ON uwdata;

-- grant system privileges
GRANT create session, create table, create procedure, create synonym, create view TO ebradmin;
GRANT select ON dba_editions TO ebradmin;
GRANT select ON dba_objects_ae TO ebradmin;
GRANT select ON dba_source_ae TO ebradmin;
GRANT create any edition TO ebradmin;
GRANT drop any edition TO ebradmin;

-- enable editions for the application owner and user
ALTER USER ebradmin ENABLE EDITIONS;
ALTER USER uwclass ENABLE EDITIONS;

-- review data dictionary entries
SELECT username, account_status, created, editions_enabled
FROM dba_users
WHERE username IN ('EBRADMIN', 'UWCLASS');

SELECT *
FROM dba_sys_privs
WHERE grantee IN ('EBRADMIN', 'UWCLASS')
ORDER BY 1,2;

SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
ORDER BY 2,1;
 
Edition Basics 1: CREATE and DROP EDITION
This demonstration covers the first step in mastering EBR. Creating an edition and navigating between editions. The string returned by the function demo_func is hardcoded to eliminate any misinterpretation of which function is visible to the user.
conn ebradmin/ebradmin

-- view default edition
SELECT * FROM dba_editions;

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

-- create and test a function under the default edition
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN 'ORA$BASE';
END demo_func;
/

SELECT demo_func FROM dual;

-- examine data dictionary metadata
desc user_objects

SELECT object_name, object_type, edition_name
FROM user_objects;

-- create a child edition
CREATE EDITION demo_ed;
-- Oracle recommends that the "AS CHILD OF" syntax not be used at this time

SELECT * FROM dba_editions;

sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

conn / as sysdba

SELECT owner, object_type, edition_name, COUNT(*)
FROM dba_objects_ae
WHERE edition_name IS NOT NULL
GROUP BY owner, object_type, edition_name;

conn ebradmin/ebradmin

-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;

sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

-- the editionable object was "copied" into the child edition
SELECT demo_func FROM dual;

-- drop the function from the child edition
DROP FUNCTION demo_func;

-- the pointer to demo_func in the child edition has been deleted
SELECT object_name, object_type, edition_name
FROM user_objects;

-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;

sho edition

-- dropping the child edition does not alter the object in the parent edition
SELECT object_name, object_type, edition_name
FROM user_objects;

-- commenting an edition
col comments format a50

SELECT * FROM all_edition_comments;

COMMENT ON EDITION demo_ed IS 'This is a demonstration edition';

SELECT * FROM all_edition_comments;

DROP EDITION demo_ed;
 
Edition Basics 2: Editioning A PL/SQL Object
This demonstration covers the second step in mastering EBR. Creating an editionable object, in this case a simple function written in PL/SQL and observing how both objects exist and function in the same schema at the same time: Their independence maintained by editioning.
conn ebradmin/ebradmin

-- create a child edition
CREATE EDITION demo_ed;

-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;

sho edition

-- editionable object was copied into the new edition
SELECT demo_func FROM dual;

-- recreate and test a function with the same name but different return value
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN 'DEMO_ED';
END demo_func;
/

-- should there be a compilation error
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN 'DEMO_ED';;
END demo_func;
/

SELECT * FROM user_errors;

SELECT * FROM user_errors_ae;


-- test the new function
SELECT demo_func FROM dual;

-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 1;
-- user_objects shows only the current edition

-- user_objects_ae and user_source_ae show All Editions (thus the AE)

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;

col text format a60

SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;

-- drop the function in the child edition
DROP FUNCTION demo_func;

-- replace the function with a procedure of the same name and test it
CREATE OR REPLACE PROCEDURE demo_func AUTHID CURRENT_USER IS
BEGIN
  dbms_output.put_line('DEMO_ED');
END demo_func;
/

set serveroutput on

exec demo_func;

-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;

SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;

-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;

show edition

-- attempt to drop the edition
DROP EDITION demo_ed;

-- learn from the exception then drop the edition
DROP EDITION demo_ed CASCADE;
 
Edition Basics 3: NON-EXISTENT Objects
It can be a bit of a shock seeing a "NON-EXISTENT" object. So see it now and understand what it is (or isn't).
conn ebradmin/ebradmin

DROP FUNCTION demo_func;

SELECT object_name, object_type
FROM user_objects
ORDER BY 1;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1,2;
 
Edition Basics 4: Switch Edition
In this final demo we start with a stored procedure that inserts 5 records into a table. Execute on the procedure and select on the table are granted to a second, application client, user. This demo is intended to show how the grant of EXECUTE by the application to the client is edition independent.
conn ebradmin/ebradmin

-- create the application objects
CREATE TABLE ebr_tab (
testcol VARCHAR2(20));

CREATE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'rgan','gens') testcol
FROM ebr_tab;

CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab;

CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  FOR i IN 1 .. 5 LOOP
    INSERT INTO ebr_tab (testcol) VALUES (instr);
  END LOOP;
  COMMIT;
END test_proc;
/

-- grant privileges to the end-user
GRANT execute ON test_proc TO uwclass;
GRANT select ON ebr_tab TO uwclass;
GRANT select ON ebr_tab_view TO uwclass;
 
-- test the application
conn uwclass/uwclass

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;


>conn ebradmin/ebradmin

-- clean up the test data
TRUNCATE TABLE ebr_tab;

-- create the child edition and the child's version of the procedure
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

SELECT object_name, object_type, edition_name
FROM user_objects_ae;

CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO ebr_tab (testcol) VALUES (REVERSE(instr));
  END LOOP;
  COMMIT;
END test_proc;
/

CREATE OR REPLACE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'aeiou','uoiea') testcol
FROM ebr_tab;

CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab_view;

SELECT object_name, object_type, edition_name
FROM user_objects_ae;

-- test the application
conn uwclass/uwclass

-- who and where are we?
sho user
sho edition

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;

ALTER SESSION SET EDITION=demo_ed;

-- learn from the exception
-- how does uwclass get access to the new edition?

-- return to the application administrator and grant the required privilege
conn ebradmin/ebradmin

GRANT use ON EDITION demo_ed TO uwclass;

-- retest the application
conn uwclass/uwclass

-- who and where are we?
sho user
sho edition

ALTER SESSION SET EDITION=demo_ed;

sho edition

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;

-- remove the client's access to the edition
conn ebradmin/ebradmin

REVOKE use ON EDITION demo_ed FROM uwclass;

DROP EDITION demo_ed CASCADE;
This concludes Demo 1. The second demo, which is linked below extends the scope to include Editioning Views.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved