Oracle11gr2新增版本功能（二）

在11.2中，Oracle資料庫引入的版本的概念，這為應用程式的升級提供了極大的方便。

這篇簡單描述版本的實現和查詢方式。

Oracle11gr2新增版本功能（一）：http://yangtingkun.itpub.net/post/468/491458

 

 

前一篇簡單描述了版本，下面接著上面的例子看看Oracle是如何實現這個功能的：

SQL> select synonym_name, table_name                   
  2  from user_synonyms;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
S_1                            T

SQL> select object_id, object_name, object_type, edition_name
  2  from user_objects
  3  where object_name = 'S_1';

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE         EDITION_NAME
---------- ------------------------------ ------------------- ------------------------------
     74125 S_1                            SYNONYM             ORA$BASE

SQL> select sys_context('USERENV', 'CURRENT_EDITION_NAME')
  2  from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
-------------------------------------------------------------------------------------
ORA$BASE

下面設定版本為MY_EDITION：

SQL> alter session set edition = my_edition;

會話已更改。

SQL> select synonym_name, table_name  
  2  from user_synonyms;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
S_1                            T1

SQL> select object_id, object_name, object_type, edition_name
  2  from user_objects
  3  where object_name = 'S_1';

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE         EDITION_NAME
---------- ------------------------------ ------------------- ------------------------------
     74128 S_1                            SYNONYM             MY_EDITION

可以看到在USER_SYNONYMS檢視中可以只能看到當前版本的同義詞定義。而在USER_OBJECTS中可以看到，兩個同義詞的名稱相同，但是兩個物件的版本名稱不同，而且OBJECT_ID並不相同，也就是說，二者實際上不是同一個物件。

SQL> select obj#, owner#, name, type#
  2  from sys.obj$
  3  where name = 'S_1';

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
     74125         84 S_1                                     5
     74128         86 S_1                                     5

檢查SYS.OBJ$發現，兩個物件不但OBJECT_ID不同，連OWNER也是不同的。

SQL> select user#, name, type#, ext_username
  2  from sys.user$
  3  where user# in (84, 86);

     USER# NAME                                TYPE# EXT_USERNAME
---------- ------------------------------ ---------- ------------------------------
        84 YANGTK                                  1
        86 SYS_LNLQ7CWEC5SUF_0IC_Q_ONI8GO          2 YANGTK

當切換到不同的版本時，Oracle實際上切換到了不同的使用者。

而使用者查詢的是當前版本的物件，這時由於很多的靜態資料字典都發生了變化：

SQL> select text
  2  from dba_views
  3  where view_name = 'DBA_SYNONYMS';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys."_CURRENT_EDITION_OBJ" o
where o.obj# = s.obj#
  and o.type# = 5
  and o.owner# = u.user#

可以看到，這些支援版本物件的資料字典資訊，訪問的系統表不再是SYS.OBJ$，而是另外一個物件：SYS._CURRENT_EDITION_OBJ。

SQL> select owner, object_name, object_id, object_type
  2  from dba_objects
  3  where object_name = '_CURRENT_EDITION_OBJ';

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS                            _CURRENT_EDITION_OBJ                 3070 VIEW

這個物件是一個檢視，檢視檢視的定義：

SQL> select text
  2  from dba_views
  3  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
        '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')

                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'))
               )
          )
      )

ORACLE就是透過將這個檢視代替了資料字典中的OBJ$物件，從而使得使用者可以查詢當前的版本資訊。