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.