Re: What is an edition?

Mladen Gogala schrieb:
> Server is 11.1.0.7 on Linux/x86. I noticed processes sometimes holding an
> "edition lock" on object ORA$BASE. The
>
> V$LOCK_TYPE table says the following: "Prevent Dropping an edition in use
> ". To make things even more confusing,
>
> there is a very well hidden view named '_CURRENT_EDITION_OBJECT'. Here is
> the text:
>
>
>
> <code>select text
> from dba_views where view_name = '_CURRENT_EDITION_OBJ';
>
>
>
> TEXT
>
> --------------------------------------------------------------------------------
>
>
> select
> o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TY
>
>
> PE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FL
>
>
> AGS",o."OID
> $",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",
>
>
>
>
> o.spare3,
>
> case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or
>
> bitand(u.spare1, 16) = 0) then
>
> null
>
>
> when (u.type# = 2) then
>
> (select eo.name from obj$ eo where eo.obj# = u.spare2)
>
> else
>
>
>
> TEXT
>
> --------------------------------------------------------------------------------
>
>
> 'ORA$BASE'
>
> end
>
> from obj$ o, user$ u
>
> where o.owner# = u.user#
>
> and ( /* non-versionable object */
>
> ( o.type#
> not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
>
> or bitand(u.spare1, 16) = 0)
>
> /* versionable object visible in current edition
> */
>
> or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
>
> and ( (u.type# <> 2 and
>
> sys_context('userenv', 'current_edition_name')
> = 'ORA$BASE')
>
>
>
> TEXT
>
> --------------------------------------------------------------------------------
>
>
>
> or (u.type#
> = 2 and
>
> u.spare2 = sys_context('userenv', 'current_edition_id'))
>
> or exists (select 1 from obj$ o2, user$ u2
>
> where o2.type#
> = 88
>
> and o2.dataobj# = o.obj#
>
> and o2.owner# = u2.user#
>
> and u2.type# = 2
>
> and u2.spare2 =
>
> sys_context('userenv',
> 'current_edition_id'))
>
> )
>
>
>
> TEXT
>
> --------------------------------------------------------------------------------
>
> )
>
> ) </code>
>
>
>
>
>
>
> OK. Here are the questions:
>
> What is the purpose of those "edition objects"? I figured out that it is
> about sys_context, but it's still unclear? The "CURRENT_EDITION_ID"
> option is not documented as a legal option for the SYS_CONTEXT function.
> What are the
>
> users of type# 2? I don't have any in my databases. Users of type#=0 are
> roles, users of type#=1 are the normal lusers.
>
> What in the world are the users of type#=2? Last but not least, what is
> protected by locks of the type AE? Did I mention
>
> that there is no documentation about this on Metalink? Nada, nil, zilch.
> On version 10, "editions" are not recognized:
>
>
>
> SQL> select sys_context('userenv','current_edition_id') from dual;
>
> select sys_context('userenv','current_edition_id') from dual
>
> *
>
> ERROR at line 1:
>
> ORA-02003: invalid USERENV parameter
>
>
>
> Version 11 recognizes the arguments as correct:
>
> Connected.
>
> SQL> select sys_context('userenv','current_edition_id') from dual;
>
>
>
> SYS_CONTEXT('USERENV','CURRENT_EDITION_ID')
>
> --------------------------------------------------------------------------------
>
> 99
>
>
>
>
>
>
>
>
>
> I am an oracle beginner with only a few months of experience. Please,
> gurus, help me with my questions.
>
>
>

It seems to have something to do with versioning within database, some examples are provided by Dan
http://www.psoug.org/reference/editions.html (as far as i understand, currently not documented, or at least hard to find ;-)
Some comments maybe of interest in rdbms/admin/cdcore.sql

There are a plenty of Oracle error messages related to editions as well - starting at ORA-38801
( obviously, it is a very bad idea to drop edition being currently in use whatever the edition is ;-)

Best regards

Maxim Received on Tue Nov 04 2008 - 16:10:44 CST