What is an edition?
Date: Tue, 4 Nov 2008 21:11:51 +0000 (UTC)
Message-ID: <geqdqn$bu7$1@registered.motzarella.org>
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.
-- http://mgogala.freehostia.comReceived on Tue Nov 04 2008 - 15:11:51 CST