Oracle11gr2新增版本功能（二）

 

在11.2中，Oracle数据库引入的版本的概念，这为应用程序的升级提供了极大的方便。

这篇简单描述版本的实现和查询方式。

 

前一篇简单描述了版本，下面接着上面的例子看看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$对象，从而使得用户可以查询当前的版本信息。

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）