网站百度权重,品牌的宣传及推广,公司网站建设的会计分录,简约好看的网站在11.2中#xff0c;Oracle数据库引入的版本的概念#xff0c;这为应用程序的升级提供了极大的方便。这篇简单描述版本的实现和查询方式。前一篇简单描述了版本#xff0c;下面接着上面的例子看看Oracle是如何实现这个功能的#xff1a;SQL select synonym_name, table…在11.2中Oracle数据库引入的版本的概念这为应用程序的升级提供了极大的方便。这篇简单描述版本的实现和查询方式。前一篇简单描述了版本下面接着上面的例子看看Oracle是如何实现这个功能的SQL select synonym_name, table_name2 from user_synonyms;SYNONYM_NAME TABLE_NAME------------------------------ ------------------------------S_1 TSQL select object_id, object_name, object_type, edition_name2 from user_objects3 where object_name S_1;OBJECT_ID OBJECT_NAME OBJECT_TYPE EDITION_NAME---------- ------------------------------ ------------------- ------------------------------74125 S_1 SYNONYM ORA$BASESQL select sys_context(USERENV, CURRENT_EDITION_NAME)2 from dual;SYS_CONTEXT(USERENV,CURRENT_EDITION_NAME)-------------------------------------------------------------------------------------ORA$BASE下面设置版本为MY_EDITIONSQL alter session set edition my_edition;会话已更改。SQL select synonym_name, table_name2 from user_synonyms;SYNONYM_NAME TABLE_NAME------------------------------ ------------------------------S_1 T1SQL select object_id, object_name, object_type, edition_name2 from user_objects3 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 574128 86 S_1 5检查SYS.OBJ$发现两个对象不但OBJECT_ID不同连OWNER也是不同的。SQL select user#, name, type#, ext_username2 from sys.user$3 where user# in (84, 86);USER# NAME TYPE# EXT_USERNAME---------- ------------------------------ ---------- ------------------------------84 YANGTK 186 SYS_LNLQ7CWEC5SUF_0IC_Q_ONI8GO 2 YANGTK当切换到不同的版本时Oracle实际上切换到了不同的用户。而用户查询的是当前版本的对象这时由于很多的静态数据字典都发生了变化SQL select text2 from dba_views3 where view_name DBA_SYNONYMS;TEXT--------------------------------------------------------------------------------select u.name, o.name, s.owner, s.name, s.nodefrom sys.user$ u, sys.syn$ s, sys._CURRENT_EDITION_OBJ owhere o.obj# s.obj#and o.type# 5and o.owner# u.user#可以看到这些支持版本对象的数据字典信息访问的系统表不再是SYS.OBJ$而是另外一个对象SYS._CURRENT_EDITION_OBJ。SQL select owner, object_name, object_id, object_type2 from dba_objects3 where object_name _CURRENT_EDITION_OBJ;OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE------------------------------ ------------------------------ ---------- -------------------SYS _CURRENT_EDITION_OBJ 3070 VIEW这个对象是一个视图查看视图的定义SQL select text2 from dba_views3 where view_name _CURRENT_EDITION_OBJ;TEXT--------------------------------------------------------------------------------select o.OBJ#,o.DATAOBJ#,o.OWNER#,o.NAME,o.NAMESPACE,o.SUBNAME,o.TYPE#,o.CTIME,o.MTIME,o.STIME,o.STATUS,o.REMOTEOWNER,o.LINKNAME,o.FLAGS,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) orbitand(u.spare1, 16) 0) thennullwhen (u.type# 2) then(select eo.name from obj$ eo where eo.obj# u.spare2)elseORA$BASEendfrom obj$ o, user$ uwhere 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 andsys_context(userenv, current_edition_name) ORA$BASE)or (u.type# 2 andu.spare2 sys_context(userenv, current_edition_id))or exists (select 1 from obj$ o2, user$ u2where o2.type# 88and o2.dataobj# o.obj#and o2.owner# u2.user#and u2.type# 2and u2.spare2 sys_context(userenv, current_edition_id)))))ORACLE就是通过将这个视图代替了数据字典中的OBJ$对象从而使得用户可以查询当前的版本信息。
