oracle 命名空间详解

一.初识NamespaceOracle通过namespace来管理schemaobject的名字，关于Namespace的定义，在官网文档上没有找到一个详细的定义，在网上搜到一些相关信息：SchemaObjectNamespacesAnamespacedefinesagroupofobjecttypes,withinwhichallnamesmustbeuniquelyidentified—byschemaandname.Objectsindifferentnamespacescansharethesamename.TheOracledatabaseusesnamespacestoresolveschemaobjectreferences.WhenyourefertoanobjectinaSQLstatement,OracleconsidersthecontextoftheSQLstatementandlocatestheobjectintheappropriatenamespace.Afterlocatingtheobject,Oracleperformstheoperationspecifiedbythestatementontheobject.Ifthenamedobjectcannotbefoundintheappropriatenamespace,thenOraclereturnsanerror.Becausetablesandviewsareinthesamenamespace,atableandaviewinthesameschemacannothavethesamename.However,tablesandindexesareindifferentnamespaces.Therefore,atableandanindexinthesameschemacanhavethesamename.Eachschemainthedatabasehasitsownnamespacesfortheobjectsitcontains.Thismeans,forexample,thattwotablesindifferentschemasareindifferentnamespacesandcanhavethesamename.--以上解释提到了几点：1•每个用户都有自己对应的namespace来保存自己的对象2•表和视图存放在同一个namespace，所以对于同一个用户的表和视图不能重名，但是表和索引是存放在不同的namespace，所以可以重名。开始时，我们提至UOracle通过schema和name来保证namespace中对象的唯一性。在obj$字典里owner#对应用户的ID。通过如下SQL，我们可以查看他们之间的对应关系：/*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.一个小示例验证以上结论：SYS@anqing2(rac2)>createtableanqing(idnumber);Tablecreated.SYS@anqing2(rac2)>createindexanqingonanqing(id);Indexcreated.SYS@anqing2(rac2)>createviewanqingasselect*fromanqing;createviewanqingasselect*fromanqing*ERRORatline1:ORA-00955:nameisalreadyusedbyanexistingobject以下类型的对象使用同一个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先执行如下SQL，查看每个namespace对应名称和它所包含的对象的个数:/*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.通过这个查询结果，我们可以看到一些对象使用相同的namespace。第一列的数字代表的就是namespace。关于type#的定义在Oracle的0耳$基表创建的定义SQL有明确的说明。先在$ORACLE_HOME/RDBMS/admin/bin下查找sql.bsq脚本。sql.bsq主要记录了ORACLE中的系统字典表的定义，比如过tab$,col$,obj$等，通过查询这个文件可以知道数据字典表的定义.在sql.bsq里面保存了相关的脚本信息其中就有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$中共有多少个不同的namespace:SYS@anqing2(rac2)>selectdistinctnamespacefromobj$orderbynamespace;NAMESPACE1234589212324253236385117rowsselected.SYS@anqing2(rac2)>selectdistinctnamespacefromv$librarycache;NAMESPACEBODYJAVADATASQLAREAOBJECTPIPEJAVARESOURCETABLE/PROCEDURETRIGGERINDEXJAVASOURCECLUSTER11rowsselected.注意：在这里有一个问题，我们通过distinctobj$和distinctv$librarycache查看的值不对应，而且这个值与我们obj$的注释也不一致。这个问题是关键，因为它可以引出到底Namespace是什么。看一下DSI405里对librarycacheobject所属于的namespace的定义：Librarycacheobjectsaregroupedinnamespacesaccordingtotheirtype.Eachobjectcanonlybeofonetype.Alltheobjectsofthesametypeareinthesamenamespace.Anamespacemaybeusedbymorethanonetype.Themostimportantnamespaceiscalledcursor(CRSR)andhousesthesharedSQLcursors.通过这段解释我们可以看出我们之前通过obj$看到的namespace是不全的，因为像sharedcursor这样的librarycacheobject根本就不在obj$里。可以这样来理解Namespace：Namespace是针对缓存在librarycache里的librarycacheobject来说的。我们之前在obj$里也有namespace的定义，是因为librarycacheobject有一部分的来源就是来自于数据库里已经存在的、固化的object的metadata。在DSI405中关于librarycacheobject所属于的namespace的详细说明：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文件，这个是Oracle的源代码，我们看不到它的内容，虽然这里面有我们所有namespace和其对应的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