oracle 命名空間詳解

一.初識NamespaceOracle通過namespace來管理schemaobject的名字，關(guān)于Namespace的定義，在官網(wǎng)文檔上沒有找到一個(gè)詳細(xì)的定義，在網(wǎng)上搜到一些相關(guān)信息：SchemaObjectNamespacesAnamespacedefinesagroupofobjecttypes,withinwhichallnamesmustbeuniquelyidentified—byschemaandname.Objectsindifferentnamespacescansharethesamename.TheOracledatabaseusesnamespacestoresolveschemaobjectreferences.WhenyourefertoanobjectinaSQLstatement,OracleconsidersthecontextoftheSQLstatementandlocatestheobjectintheappropriatenamespace.Afterlocatingtheobject,Oracleperformstheoperationspecifiedbythestatementontheobject.Ifthenamedobjectcannotbefoundintheappropriatenamespace,thenOraclereturnsanerror.Becausetablesandviewsareinthesamenamespace,atableandaviewinthesameschemacannothavethesamename.However,tablesandindexesareindifferentnamespaces.Therefore,atableandanindexinthesameschemacanhavethesamename.Eachschemainthedatabasehasitsownnamespacesfortheobjectsitcontains.Thismeans,forexample,thattwotablesindifferentschemasareindifferentnamespacesandcanhavethesamename.--以上解釋提到了幾點(diǎn)：1?每個(gè)用戶都有自己對應(yīng)的namespace來保存自己的對象2?表和視圖存放在同一個(gè)namespace，所以對于同一個(gè)用戶的表和視圖不能重名，但是表和索引是存放在不同的namespace，所以可以重名。開始時(shí)，我們提至UOracle通過schema和name來保證namespace中對象的唯一性。在obj$字典里owner#對應(yīng)用戶的ID。通過如下SQL，我們可以查看他們之間的對應(yīng)關(guān)系：/*Formattedon2011/7/2115:41:26(QP5v5.163.1008.3004)*/SELECTusername,user_idFROMdba_usersWHEREuser_idIN(SELECTDISTINCTowner#FROMobj$);USERNAME USER_IDSYS 0SYSTEM 5TOC\o"1-5"\h\zDBSNMP 24SYSMAN 58DAVE 61OUTLN11MDSYS 46ORDSYS 43CTXSYS 36EXFSYS 34DMSYS 35WMSYS 25XDB38ORDPLUGINS44SI_INFORMTN_SCHEMA45OLAPSYS4754SCOTT54TSMSYS2119rowsselected.一個(gè)小示例驗(yàn)證以上結(jié)論：SYS@anqing2(rac2)>createtableanqing(idnumber);Tablecreated.SYS@anqing2(rac2)>createindexanqingonanqing(id);Indexcreated.SYS@anqing2(rac2)>createviewanqingasselect*fromanqing;createviewanqingasselect*fromanqing*ERRORatline1:ORA-00955:nameisalreadyusedbyanexistingobject以下類型的對象使用同一個(gè)namespace：?Tables?Views?Sequences?Privatesynonyms?Stand-aloneprocedures?Stand-alonestoredfunctions?Packages?Materializedviews?User-definedtypes如下類型的對象使用自己的namespace：?Indexes?Constraints?Clusters?Databasetriggers?Privatedatabaselinks?Dimensions以下Nonschemaobjects使用自己的namespace：?Userroles?Publicsynonyms?Publicdatabaselinks?Tablespaces?Profiles?Parameterfiles(PFILEs)andserverparameterfiles(SPFILEs)以上信息隨Oracle版本不同，可能有出入。二.深入研究Namespace先執(zhí)行如下SQL，查看每個(gè)namespace對應(yīng)名稱和它所包含的對象的個(gè)數(shù):/*Formattedon2011/7/2114:24:47(QP5v5.163.1008.3004)*/SELECTnamespace,object_type,COUNT(*)FROM(SELECTnamespace,DECODE(o.type#,0,'NEXTOBJECT','INDEX','TABLE','CLUSTER','VIEW','SYNONYM','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY','TRIGGER','TYPE','TYPEBODY','TABLEPARTITION','INDEXPARTITION','LOB','LIBRARY','DIRECTORY','QUEUE','JAVASOURCE','JAVACLASS','JAVARESOURCE','INDEXTYPE','OPERATOR','TABLESUBPARTITION','INDEXSUBPARTITION','LOBPARTITION','LOBSUBPARTITION','MATERIALIZEDVIEW','DIMENSION','CONTEXT',

'RULESET','RESOURCEPLAN','CONSUMERGROUP',51,'SUBSCRIPTION',52,'LOCATION','XMLSCHEMA','JAVADATA','SECURITYPROFILE',59,'RULE',62,'EVALUATIONCONTEXT','UNDEFINED')object_typeFROMsys.obj$o)GROUPBYnamespace,object_type;NAMESPACEOBJECT_TYPECOUNT(*)22534INDEX22532TYPEBODY17551UNDEFINED621CONTEXT59DIRECTORY61SYNONYM201221PACKAGE8591VIEW36841PROCEDURE983TRIGGER16438EVALUATIONCONTEXT121SEQUENCE1382PACKAGEBODY8041INDEXTYPE1019531TYPE1953TOC\o"1-5"\h\z24RESOURCEPLAN34INDEXPARTITION 14425XMLSCHEMA 251TABLE 16198LOB 54110QUEUE 2323RULESET 158LOBPARTITION136RULE41JAVACLASS 164501NEXTOBJECT11FUNCTION 2681UNDEFINED 6601541LIBRARY1545CLUSTER1024CONSUMERGROUP51TABLEPARTITION 1241OPERATOR5714JAVARESOURCE 77535rowsselected.通過這個(gè)查詢結(jié)果，我們可以看到一些對象使用相同的namespace。第一列的數(shù)字代表的就是namespace。關(guān)于type#的定義在Oracle的0耳$基表創(chuàng)建的定義SQL有明確的說明。先在$ORACLE_HOME/RDBMS/admin/bin下查找sql.bsq腳本。sql.bsq主要記錄了ORACLE中的系統(tǒng)字典表的定義，比如過tab$,col$,obj$等，通過查詢這個(gè)文件可以知道數(shù)據(jù)字典表的定義.在sql.bsq里面保存了相關(guān)的腳本信息其中就有dcore.bsq腳本，在該腳本里就可以找到obj$表的定義SQL:/*Formattedon2011/7/2114:42:51(QP5v5.163.1008.3004)*/CREATETABLEobj$(obj#NUMBERNOTNULL,dataobj#NUMBER,owner#NUMBERNOTNULL,nameVARCHAR2("M_IDEN")notnull,CREATETABLEobj$(obj#NUMBERNOTNULL,dataobj#NUMBER,owner#NUMBERNOTNULL,nameVARCHAR2("M_IDEN")notnull,/*objecttable*//*objectnumber*//*datalayerobjectnumber*//*ownerusernumber*//*objectname*/namespacenumbernotnull,/*namespaceofobject(seeKQD.H):*//*1=TABLE/PROCEDURE/TYPE,2=BODY,3=TRIGGER,4=INDEX,5=CLUSTER,*//*8=LOB,9=DIRECTORY,*//*10=QUEUE,11=REPLICATIONOBJECTGROUP,12=REPLICATIONPROPAGATOR,*//*13=JAVASOURCE,14=JAVARESOURCE*//*subordinatetothename*//*58=(DataMining)MODEL*//*subordinatetothename*/subnamevarchar2("M_IDEN"),

type#numbernotnull,/*objecttype(seeKQD.H):*/type#numbernotnull,/*objecttype(seeKQD.H):*//*1=INDEX,2=TABLE,3=CLUSTER,4=VIEW,5=SYNONYM,6=SEQUENCE,*//*7=PROCEDURE,8=FUNCTION,9=PACKAGE,10=NON-EXISTENT,*//*11=PACKAGEBODY,12=TRIGGER,13=TYPE,14=TYPEBODY,*//*19=TABLEPARTITION,20=INDEXPARTITION,21=LOB,22=LIBRARY,*//*23=DIRECTORY,24=QUEUE,*//*25=IOT,26=REPLICATIONOBJECTGROUP,27=REPLICATIONPROPAGATOR,*//*28=JAVASOURCE,29=JAVACLASS,30=JAVARESOURCE,31=JAVAJAR,*//*32=INDEXTYPE,33=OPERATOR,34=TABLESUBPARTITION,*//*35=INDEXSUBPARTITION*//*82=(DataMining)MODEL*//*92=OLAPCUBEDIMENSION,93=OLAPCUBE*//*94=OLAPMEASUREFOLDER,95=OLAPCUBEBUILDPROCESS*/

ctimedatenotnull,/*objectcreationtime*/mtimedatenotnull,/*DDLmodificationtime*/ctimedatenotnull,/*objectcreationtime*/mtimedatenotnull,/*DDLmodificationtime*/stimedatenotnull,statusnumbernotnull,/*specificationtimestamp(version)*/stimedatenotnull,statusnumbernotnull,/*statusofobject(seeKQD.H):*//*1=VALID/AUTHORIZEDWITHOUTERRORS,*//*2=VALID/AUTHORIZEDWITHAUTHORIZATIONERRORS,*//*3=VALID/AUTHORIZEDWITHCOMPILATIONERRORS,*//*4=VALID/UNAUTHORIZED,5=INVALID/UNAUTHORIZED*/remoteownervarchar2("M_IDEN"),/*remoteownername(remoteobject)*/linknamevarchar2("M_XDBI"),/*linkname(remoteobject)*/flagsnumber,/*0x01=extentmapcheckingrequired*//*0x02=temporaryobject*//*0x04=systemgeneratedobject*//*0x08=unbound(invoker'srights)*//*0x10=secondaryobject*/

/*0x10=secondaryobject*//*0x80=droppedtable(RecycleBin)*//*0x100=synonymVPDpolicies*//*0x200=synonymVPDgroups*//*0x400=synonymVPDcontext*//*0x4000=nestedtablepartition*/oid$raw(16),/*OIDfortypedtable,typedview,andtype*/spare1number,/*sqlversionflag:seekpul.h*/spare2number,/*objectversionnumber*/spare3number,/*baseuser#*/spare4varchar2(1000),spare5varchar2(1000),spare6datestorage(initial10knext100kmaxextentsunlimitedpctincrease0)obj$里有說明。namespacenumbernotnull,/*namespaceofobject(seeKQD.H):*/1=TABLE/PROCEDURE/TYPE,2=BODY,3=TRIGGER,4=INDEX,5=CLUSTER,8=LOB,9=DIRECTORY,10=QUEUE,11=REPLICATIONOBJECTGROUP,12=REPLICATIONPROPAGATOR,13=JAVASOURCE,14=JAVARESOURCE58=(DataMining)MODEL查看obj$中共有多少個(gè)不同的namespace:SYS@anqing2(rac2)>selectdistinctnamespacefromobj$orderbynamespace;NAMESPACE1234589212324253236385117rowsselected.SYS@anqing2(rac2)>selectdistinctnamespacefromv$librarycache;NAMESPACEBODYJAVADATASQLAREAOBJECTPIPEJAVARESOURCETABLE/PROCEDURETRIGGERINDEXJAVASOURCECLUSTER11rowsselected.注意：在這里有一個(gè)問題，我們通過distinctobj$和distinctv$librarycache查看的值不對應(yīng)，而且這個(gè)值與我們obj$的注釋也不一致。這個(gè)問題是關(guān)鍵，因?yàn)樗梢砸龅降譔amespace是什么?？匆幌翫SI405里對librarycacheobject所屬于的namespace的定義：Librarycacheobjectsaregroupedinnamespacesaccordingtotheirtype.Eachobjectcanonlybeofonetype.Alltheobjectsofthesametypeareinthesamenamespace.Anamespacemaybeusedbymorethanonetype.Themostimportantnamespaceiscalledcursor(CRSR)andhousesthesharedSQLcursors.通過這段解釋我們可以看出我們之前通過obj$看到的namespace是不全的，因?yàn)橄駍haredcursor這樣的librarycacheobject根本就不在obj$里。可以這樣來理解Namespace：Namespace是針對緩存在librarycache里的librarycacheobject來說的。我們之前在obj$里也有namespace的定義，是因?yàn)閘ibrarycacheobject有一部分的來源就是來自于數(shù)據(jù)庫里已經(jīng)存在的、固化的object的metadata。在DSI405中關(guān)于librarycacheobject所屬于的namespace的詳細(xì)說明：Currentlythereare64differentobjecttypesbutthisnumbermaygrowatanytimewiththeintroductionofnewfeatures.Examplesoftypesare:cursor,table,synonym,sequence,index,LOB,Javasource,outline,dimension,andsoon.Noteverytypecorrespondstoanamespace.Actually,thereareonly32namespaceswhich,ofcourse,arealsosubjecttoincreaseatanytime.Youcanseethelistofnamespacesinthelibrarycachedump.Whatisacertaintyisthatalltheobjectsofthesametypewillalwaysbestoredinthesamenamespace.Anobjectcanonlybeofonetype,hencethesearchforanobjectinthelibrarycacheisreducedtoasearchforthisobjectinthecorrespondingnamespace.Somenamespacescontainobjectsoftwoorthreedifferenttypes.Thesearesomeofthemostcommonlyusednamespaces:CRSR:Storeslibraryobjectsoftypecursor(sharedSQLstatements)TABL/PRCD/TYPE:Storestables,views,sequences,synonyms,procedurespecifications,functionspecifications,packagespecifications,libraries,andtypespecificationsBODY/TYBD:Storesprocedure,function,package,andtypebodiesTRGR:StoreslibraryobjectsoftypetriggerINDX:StoreslibraryobjectsoftypeindexCLST:StoreslibraryobjectsoftypeclusterTheexactnumberandnameofnamespacesinusedependsontheserverfeaturesthatareusedbytheapplication.Forexample,iftheapplicationusesJava,namespaceslikeJVSC(Javasource)andJVRE(Javaresource)maybeused,otherwisetheywillnotbeused.Note:Thesenamespacesdonotstoretables,clusters,orindexesassuch,onlythemetadataisstored.在obj$的注釋里提到了KQD.H文件，這個(gè)是Oracle的源代碼，我們看不到它的內(nèi)容，雖然這里面有我們所有namespace和其對應(yīng)的namespaceid。不過我們可以dumplibrarycache，來查看該版本下所有namespace的名稱。版本不同，namespace也可能不同。--查看Oracle版本SYS@anqing2(rac2)>select*fromv$versionwhererownum=1;BANNEROracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-ProdSYS@anqing2(rac2)>oradebugsetmypidStatementprocessed.--把librarycachedump出來SYS@anqing2(rac2)>altersessionsetevents'immediatetracenamelibrary_cachelevel1';Sessionaltered.--獲取tracefile名稱和路徑SYS@anqing2(rac2)>oradebugtracefile_name/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trc[oracle@rac2~]$cat/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trc/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trcOracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-ProductionWiththePartitioning,RealApplicationClusters,OLAP,DataMiningandRealApplicationTestingoptionsORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1Systemname:LinuxNodename:rac2Release: 2.6.18-194.el5Version: #1SMPTueMar1621:52:43EDT2010Machine:i686Instancename:anqing2Redothreadmountedbythisinstance:2Oracleprocessnumber:23Unixprocesspid:18783,image:oracle@rac2(TNSV1-V3)***2011-07-2119:23:32.578***ACTIONNAME:()2011-07-2119:23:32.574***MODULENAME:(sqlplus@rac2(TNSV1-V3))2011-07-2119:23:32.574***SERVICENAME:(SYS$USERS)2011-07-2119:23:32.574***SESSIONID:(128.4091)2011-07-2119:23:32.574LIBRARYCACHESTATISTICS:namespacegetshitratiopinshitratioreloadsinvalidsCRSR 1640170.97846937450.999373123TABL 62538 0.9661408421 0.8681829570BODY15750.97019370.96780TRGR2310.8182840.85200INDX60 0.183 107 0.430 4 0CLST 281 0.954 720 0.982 0 0KGLT00.00000.00000

PIPE00.00000.00000LOB00.00000.00000DIR20.50040.50000QUEU30.000130.46210OBJG00.00000.00000PROP00.00000.00000JVSC00.00000.00000JVRE00.00000.00000ROBJ00.00000.00000REIP00.00000.00000CPOB00.00000.00000EVNT157731.000159150.9991SUMM00.00000.00000DIMN00.00000.00000CTX00.00000.00000OUTL 0 0.000 0 0.000 0 0RULS 1 0.000 3 0.667 0 0RMGR321591.000396191.00010XDBS70.28670.00000PPLN00.00000.00000PCLS00.00000.00000SUBS20.50020.50000LOCS00.00000.00000RMOB00.00000.00000RSMD00.00000.00000JVSD00.00000.00000STFG00.00000.00000TRANS00.00000.00000RELC00.00000.00000RULE00.00000.00000STRMSTRM00.000REVC10.000STAP00.000RELS00.000RELD00.000IFSD00.000XDBC10.000USAG00.000MVOBTBL10.000JSQI00.000CDC00.000MVOBIND10.000STBO00.000HTSO00.000JSGA98050.99900.00000TOC\o"1-5"\h\z0 0.000 0 00 0.000 0 00 0.000 0 00 0.000 0 000.0000010.000 0 000.000 0 010.0000000.0000000.0000010.0000000.000 0 000.000 0 031520850.9421817630JSET70.00070.00000TABL_T200.400200.00080CLST_T00.0000/r