How many editions ?
The answer here is 2. Or 3 if you count ORA$BASE, which I will be ignoring in these discussions, so 2.
What schemas will contain editioned objects ?
Create this view in your private account to help you analyze the existing schemas. You will probably need an explicit grant to dba_objects, or use all_objects instead.
CREATE OR REPLACE VIEW editioned_users_obj_cnt AS
SELECT owner, object_type, COUNT(*) obj_count FROM dba_objects o
WHERE owner NOT LIKE ‘%SYS%’
AND owner NOT IN (‘PUBLIC’, ‘XDB’, ‘DBSNMP’, ‘ORACLE_OCM’, ‘ORDDATA’, ‘ORDPLUGINS’, ‘OUTLN’, ‘SCOTT’, ‘DIP’, ‘OPS$ORACLE’, ‘ORACLE’)
AND object_type IN (‘FUNCTION’, ‘PACKAGE’, ‘PROCEDURE’, ‘TABLE’, ‘TRIGGER’, ‘TYPE’, ‘VIEW’)
GROUP BY owner, object_type ORDER BY owner, object_type;
Once you have that, query and investigate whether you want/need all of them to be editioned. If in doubt, the answer is probably yes.
This query will show all the schemas you should probably edition:
SELECT DISTINCT owner, editions_enabled FROM editioned_users_obj_cnt JOIN dba_users ON (username = owner);
Naming conventions
editions – pick 2 names, I don’t think they should reflect an order. Colors work well, let’s say “RED” and “BLACK”
tables vs editioning views – We will append an underscore to each table name. If that makes the table name too long, we will change the final char to an underscore. Any remaining name conflicts will be resolved manually. This SQL will generate the new table names, it calls the old table name the “EV _NAME” (Editioning View):
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;
This SQL will show any namespace collisions with the new tables names that were generated. If it returns no rows, there are no issues.
SELECT owner, substr(table_name, 1, 29) || ‘_’ new_table_name, COUNT(*) FROM dba_tables
WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt) AND length(table_name) = 30
GROUP BY owner, substr(table_name, 1, 29) || ‘_’ HAVING COUNT(*) > 1;
Will you use cross edition triggers
For now, no. This can be revisited later if needed, but it removes a lot of extra training and confusion to say no at this point.
Handling DML changes to live systems
All DML done during deploy windows must be backward compatible with the former (live while we are releasing) code. Violations of this must be noted and a custom plan made for dealing with 2 code versions running against the same data. If aware of this pitfall, developers can usually code around this.
Cleanup tasks
We will drop or otherwise cleanup any invalid objects. This can be handled well ahead of time, with some monitoring in place to keep it clean.
Wherever possible, we will find code dependencies on synonyms and make the reference explicit. This SQL identifies dependencies on synonyms inside the database:
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;