SELECT ‘alter table ‘ || owner || ‘.’ || ev_name || ‘ rename to ‘ || new_table_name || ‘;’ ||
chr(10) || ‘create editioning view ‘ || owner || ‘.’ || ev_name || ‘ as select * from ‘ ||
owner || ‘.’ || new_table_name || ‘;’
FROM (SELECT owner, table_name ev_name, table_name || ‘_’ new_table_name FROM dba_tables
WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt) AND length(table_name) < 30
UNION
SELECT owner, table_name ev_name, substr(table_name, 1, 29) || ‘_’ new_table_name FROM dba_tables
WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt) AND length(table_name) = 30);

SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt)
MINUS
SELECT owner, table_name FROM dba_editioning_views;

SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET LINES 2047
SET PAGES 0
SET LONGCHUNKSIZE 2047
SET LONG 200000
SET TRIMSPOOL ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE);
COLUMN source_text WORD_WRAPPED
SPOOL trigger_ddl.sql
SELECT dbms_metadata.get_ddl(‘TRIGGER’, trigger_name, owner) source_text FROM dba_triggers
WHERE owner IN (SELECT owner FROM dheltzel.editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’)
AND base_object_type = ‘TABLE’;
SPOOL OFF
EXIT

SELECT synonym_name, table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt);

SELECT DISTINCT t.owner,t.name,t.type object_type,s.owner synonym_owner,s.synonym_name,s.table_owner || ‘.’ || s.table_name real_object
  FROM dba_dependencies t JOIN dba_synonyms s ON (s.owner = t.referenced_owner AND s.synonym_name = t.referenced_name AND t.referenced_type = ‘SYNONYM’)
 WHERE s.owner = ‘PUBLIC’ AND s.table_owner IN (SELECT owner FROM editioned_users_obj_cnt);

SELECT ‘create or replace synonym ‘ || grantee || ‘.’ || table_name || ‘ for ‘ || owner || ‘.’ || table_name || ‘;’
  FROM ((SELECT r.grantee, p.owner, p.table_name FROM dba_role_privs r JOIN dba_tab_privs p ON (p.grantee = r.granted_role)
         UNION
         SELECT grantee, owner, table_name FROM dba_tab_privs)

        MINUS SELECT owner, table_owner, table_name FROM dba_synonyms) a
 WHERE (owner, table_name) IN

  (SELECT table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt));

create edition RED as child of ora$base;

comment on edition RED is ‘This is the red edition’;

grant use on edition RED to PUBLIC;

create edition BLACK as child of RED;

comment on edition BLACK is ‘This is the black edition’;

grant use on edition BLACK to PUBLIC;

SELECT e.*, c.comments FROM dba_editions e JOIN dba_edition_comments c ON (c.edition_name = e.edition_name);

SELECT * FROM dba_tab_privs WHERE privilege = ‘USE’;

SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
It should return ORA$BASE

ALTER SESSION SET DDL_LOCK_TIMEOUT=30;
ALTER DATABASE DEFAULT EDITION=RED;

SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
This time it should return ‘RED’

SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) FROM dual;

SELECT DISTINCT t.owner,t.name,t.type object_type,s.owner synonym_owner,s.synonym_name,s.table_owner || ‘.’ || s.table_name real_object
  FROM dba_dependencies t JOIN dba_synonyms s ON

    (s.owner = t.referenced_owner AND s.synonym_name = t.referenced_name AND t.referenced_type = ‘SYNONYM’)
 WHERE t.owner NOT LIKE ‘%SYS%’ AND t.owner NOT IN (‘PUBLIC’, ‘DBSNMP’, ‘ORACLE_OCM’, ‘XDB’, ‘ORDPLUGINS’, ‘CSMIG’) AND
       t.referenced_owner NOT IN (‘PUBLIC’) AND s.db_link IS NULL AND t.type NOT IN (‘SYNONYM’)
 ORDER BY t.owner, t.type, t.name;

1. Planning steps
2. Create Editions
3. Replace Public Synonyms
4. Enable schemas for editioning
5. Save all triggers on the tables to be editioned
6. Converting tables to Editioning Views
7. Moving Triggers
8. Stabilizes all editioned objects across editions
9. Switch the default to one of the new edtions
10. Implement no-downtime code deploys

How many editions ?
What schemas will contain editioned objects ?
Naming conventions

• editions
• tables vs editioning views – example would be to append an underscore to each table name

Will you use cross edition triggers ?
Handling DML changes to live systems
Cleanup tasks

• drop any invalid objects (or make them valid if you need to keep them)
• (optional) find code dependencies on synonyms and make the reference explicit if possible

I also reserve the right to come back to edit and add to this as I go through my next implementation. This was all written from memory and nothing jogs ones memory like repeating the process a second time.

1. Determine the correct edition. If you are using the two edition method, simply find the default edition and pick the other one, hereafter known as the “deploy edition”.
2. Connect as the deploy edition using one of the methods outlined in the previous post.
3. Verify that the current edition is the deploy edition, and that it is not the default edition.
4. Verify that no other sessions that use the code you are deploying are using the deploy edition.

# Get the current default edition
DEF_EDITION=`sqlplus -s / as sysdba <<!
set head off
set feed off
set pages 0
select property_value from database_properties where property_name = ‘DEFAULT_EDITION’;
!`
# now switch the edition name
[ ${DEF_EDITION} = “BLACK” ] && DEPLOY_EDITION=WHITE || DEPLOY_EDITION=BLACK

ORA_EDITION=${DEPLOY_EDITION}
sqlplus / as sysdba

– or –

sqlplus / as sysdba edition=${DEPLOY_EDITION}

SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) “Current Edition” FROM dual;
WHENEVER SQLERROR EXIT
SET SERVEROUT ON SIZE UNLIMITED
DECLARE
e_halt_processing EXCEPTION;
v_edition varchar2(35);

  v_sess_cnt pls_integer;

BEGIN
SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) INTO v_edition FROM dual;
— check that we are in the correct edition
IF v_edition <> ‘${DEPLOY_EDITION}’ THEN
DBMS_OUTPUT.PUT_LINE(‘Deploy halted – edition not set properly!!’);
RAISE e_halt_processing;
END IF;
— check that it’s not the default edition

  SELECT property_value INTO v_edition FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
IF v_edition = ‘${DEPLOY_EDITION}’ THEN
DBMS_OUTPUT.PUT_LINE(‘Deploy halted – this is the default edition !!’);
RAISE e_halt_processing;
END IF;
— check that no app sessions are using the edition we are deploying to

SELECT COUNT(*) INTO v_sess_cnt FROM sys.gv$session s LEFT OUTER JOIN sys.dba_objects o ON (s.session_edition_id = o.object_id)
 WHERE username IN (‘APP1′,’APP2′,’APP3’)  AND s.status <> ‘KILLED’
 AND o.object_name = ‘${DEPLOY_EDITION}’;
  IF v_sess_cnt > 0 THEN
     DBMS_OUTPUT.PUT_LINE(‘Deploy halted – sessions are using this edition !!’);
     RAISE e_halt_processing;
  END IF;
END;
/
WHENEVER SQLERROR CONTINUE