working with editions – part 1

This post will list commands that are used in the scope of edition based redefinition feature of the 11g Oracle database. There will be no additional explanation of the commands. That is what the oracle documentation is good for.
Additionally see this article http://www.oracle.com/technetwork/testcontent/o10asktom-172777.html

-- create a new edition (DBA)
create edition Release0 AS CHILD OF ORA$BASE;

-- switch current session to a different edition
ALTER SESSION SET EDITION = Release0;

or

-- in PL/SQL (inside a db logon trigger)
dbms_session.SET_EDITION_DEFERRED('Release0') 
 
--show the current edition of the session
select sys_context('userenv','current_edition_name') from dual;
 
-- which users are allowed to work with editions? (DBA)
select username, editions_enabled
from dba_users;

-- switch user to allow working with editions (DBA)
ALTER USER MYSCHEMA ENABLE EDITIONS;

-- switch user to disallow working with editions 
not possible!

-- give access to a specific edition
GRANT USE ON EDITION Release0 to APEX_PUBLIC_USER;
GRANT USE ON EDITION Release0 to MYSCHEMA;
--GRANT USE ON EDITION Release0 to APEX_040100;
--GRANT USE ON EDITION Release0 to ANONYMOUS;
--GRANT USE ON EDITION Release0 to PUBLIC;

-- disallow access to a specific edition
REVOKE USE on EDITION Release0 from APEX_PUBLIC_USER;

-- set the default edition (DBA)
alter database default edition = Release0;

-- show the current default edition 
select * from database_properties where property_name like 'DEFAULT_EDITION';

-- show editions (DBA)
select * from dba_editions;
select * from dba_edition_comments;

-- remove edition (DBA)
drop edition Release0;

/* sometimes the edition can not be dropped, because there are still sessions open that use this edition.
Then we will get an
"SQL-ERROR: ORA-38805: Edition in use"

additionally the default edition can not be dropped
*/

-- double check own session
select sys_context('userenv','current_edition_name') from dual;

-- search for other sessions, incl. APEX
select s.sid, s.serial#, s.session_edition_id , o.object_name as edition_name, s.username, s.machine, s.program
from v$session s
left join all_objects o on o.object_id = s.session_edition_id;


-- remove edition including objects in that edition (DBA)
drop edition Release0 cascade;

The next post will focus on building a connection to a specific edition.

Advertisements

One thought on “working with editions – part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s