Category: oracle

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

You have your editions, and you can tell which edition each session is currently using, now how do you control that?

First we’ll look at sessions that you control. If you, or code under your control, initiates the edition, you can control the edition with an ALTER SESSION command, like this:

alter session set edition=MYEDITION;

There are shortcuts to this. When using sqlplus, you can set the ORA_EDITION env var or pass the edition on the command line like this:

sqlplus / as sysdba edition=MYEDITION

Note that although the SYS user cannot have editions enabled, it is still true that every login session has a current edition, since it may access objects owned by users that do have editions enabled.

Some other tools may allow you to set an edition for a database connection. This is really all just making the alter sessions command automatic.

You can change the edition in a session as often as you wish, but you cannot be in a place where changing the edition would make the current context invalid. For example, you may not change the edition inside PL/SQL because then you might be executing code from the old edition in the context of the new edition. I know you were thinking of login triggers to change the edition, but you can forget that.

So what about sessions that you don’t control in any way? If they are already running, you cannot change their edition. Think about the havoc that would wreck if it was allowed!

You can however influence the edition that they connect with. I say influence because the session can override your setting with an alter session of their own. The main way you do this is by changing the default edition for the entire database. Think of the default edition as the stable, steady state between code deploys. With no other intervention, all sessions connect as this edition and everything is consistent.

What if you want only certain sessions to connect with the newly deployed code in a non-default edition? The ideal way is to have those sessions make the alter session command, but if you don’t want to do that you have one final option, cluster-ready services.

The -t option to the srvctl modify service command will allow you to specify the edition for that service. Be careful in implementing this, it doesn’t always work reliably, but it’s the best option for controlling the connections of general apps. For one thing, please resist the temptation to use the dbms_services package to change the edition. It has all the options needed and will change the edition in the DBA_SERVICES view, but it will not change it in CRS correctly. Another bug is that if you try to unset the edition with the srvctl command (with -r “”), that will remove it from CRS (as shown by srvctl config), but leave it in the data dictionary, so then you must use the services package as a second step to truly clear it back to use the default edition.

My recommendation is that you not alter the edition of a service to effect this change, but create separate services for each edition and change the service name in the connect details the session will use. Editing the tnsnames.ora on the webserver for example.