Oracle Namespace 说明

一. 初识Namespace

Oracle通过namespace来管理schema object的名字，关于Namespace 的定义，在官网文档上没有找到一个详细的定义，在网上搜到一些相关信息：

SchemaObject Namespaces

            A namespace defines a group of object types, within whichall names must be uniquely identified—by schema and name. Objects in differentnamespaces can share the same name.
            The Oracle database usesnamespaces to resolve schema object references. When you refer to an object ina SQL statement, Oracle considers the context of the SQL statement and locatesthe object in the appropriate namespace. After locating the object, Oracleperforms the operation specified by the statement on the object. If the named object cannot be found in the appropriate namespace,then Oracle returns an error.
            Because tables and views are in the same namespace, a table and a view inthe same schema cannot have the same name. However, tables and indexesare in different namespaces. Therefore, a table and an index in the same schemacan have the same name.
            Eachschema in the database has its own namespaces for the objects it contains. Thismeans, for example, that two tables in different schemas are in differentnamespaces and can have the same name.

--以上解释提到了几点：

1.每个用户都有自己对应的namespace来保存自己的对象

2.表和视图存放在同一个namespace，所以对于同一个用户的表和视图不能重名，但是表和索引是存放在不同的namespace，所以可以重名。

开始时，我们提到Oracle 通过schema 和 name 来保证namespace中对象的唯一性。在obj$ 字典里owner# 对应用户的ID。通过如下SQL，我们可以查看他们之间的对应关系：

/* Formatted on 2011/7/21 15:41:26(QP5 v5.163.1008.3004) */

SELECT username,user_id

FROM dba_users

WHERE user_id IN (SELECT DISTINCTowner# FROM obj$);

USERNAME USER_ID

------------------------- ----------

SYS 0

SYSTEM 5

DBSNMP 24

SYSMAN 58

DAVE 61

OUTLN 11

MDSYS 46

ORDSYS 43

CTXSYS 36

EXFSYS 34

DMSYS 35

WMSYS 25

XDB 38

ORDPLUGINS 44

SI_INFORMTN_SCHEMA 45

OLAPSYS 47

SCOTT 54

ORACLE_OCM 55

TSMSYS 21

19 rows selected.

一个小示例验证以上结论：

SYS@anqing2(rac2)> createtable anqing(id number);

Table created.

SYS@anqing2(rac2)>create index anqing on anqing(id);

Index created.

SYS@anqing2(rac2)>create view anqing as select * from anqing;

create view anqing asselect * from anqing

ERROR at line 1:

ORA-00955: name isalready used by an existing object

以下类型的对象使用同一个namespace：

• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types

如下类型的对象使用自己的namespace：

• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions

以下Non schema objects 使用自己的namespace：

• User roles
• Public synonyms
• Public database links
• Tablespaces
• Profiles
• Parameter files (PFILEs) and server parameter files (SPFILEs)

以上信息随Oracle 版本不同，可能有出入。

二. 深入研究Namespace

先执行如下SQL，查看每个namespace 对应名称和它所包含的对象的个数：

/* Formatted on 2011/7/21 14:24:47(QP5 v5.163.1008.3004) */

SELECTnamespace,object_type, COUNT (*)

FROM (SELECTnamespace,

DECODE (o.type#,

0, 'NEXTOBJECT',

1, 'INDEX',

2, 'TABLE',

3, 'CLUSTER',

4, 'VIEW',

5, 'SYNONYM',

6, 'SEQUENCE',

7, 'PROCEDURE',

8, 'FUNCTION',

9, 'PACKAGE',

11, 'PACKAGEBODY',

12, 'TRIGGER',

13, 'TYPE',

14, 'TYPEBODY',

19, 'TABLEPARTITION',

20, 'INDEX PARTITION',

21, 'LOB',

22, 'LIBRARY',

23, 'DIRECTORY',

24, 'QUEUE',

28, 'JAVASOURCE',

29, 'JAVACLASS',

30, 'JAVARESOURCE',

32, 'INDEXTYPE',

33, 'OPERATOR',

34, 'TABLESUBPARTITION',

35, 'INDEXSUBPARTITION',

40, 'LOBPARTITION',

41, 'LOBSUBPARTITION',

42, 'MATERIALIZEDVIEW',

43, 'DIMENSION',

44, 'CONTEXT',

46, 'RULESET',

47, 'RESOURCEPLAN',

48, 'CONSUMERGROUP',

51, 'SUBSCRIPTION',

52, 'LOCATION',

55, 'XMLSCHEMA',

56, 'JAVADATA',

57, 'SECURITYPROFILE',

59, 'RULE',

62, 'EVALUATIONCONTEXT',

'UNDEFINED')

object_type

FROM sys.obj$ o)

GROUP BYnamespace,object_type;

NAMESPACE OBJECT_TYPE COUNT(*)

---------- ------------------ ----------

4 INDEX 2253

2 TYPE BODY 175

51 UNDEFINED 6

21 CONTEXT 5

9 DIRECTORY 6

1 SYNONYM 20122

1 PACKAGE 859

1 VIEW 3684

1 PROCEDURE 98

3 TRIGGER 164

38 EVALUATION CONTEXT 12

1 SEQUENCE 138

2 PACKAGE BODY 804

1 INDEXTYPE 10

1 TYPE 1953

24 RESOURCE PLAN 3

4 INDEX PARTITION 144

25 XML SCHEMA 25

1 TABLE 1619

8 LOB 541

10 QUEUE 23

23 RULE SET 15

8 LOB PARTITION 1

36 RULE 4

1 JAVA CLASS 16450

1 NEXT OBJECT 1

1 FUNCTION 268

1 UNDEFINED 660

1 LIBRARY 154

32 JAVA DATA 298

5 CLUSTER 10

24 CONSUMER GROUP 5

1 TABLE PARTITION 124

1 OPERATOR 57

14 JAVA RESOURCE 775

35 rows selected.

通过这个查询结果，我们可以看到一些对象使用相同的namespace。第一列的数字代表的就是namespace。关于type#的定义，在Oracle的obj$基表创建的定义SQL 有明确的说明。

先在$ORACLE_HOME/RDBMS/admin/bin 下查找sql.bsq脚本。sql.bsq主要记录了ORACLE中的系统字典表的定义，比如过tab$,col$,obj$ 等，通过查询这个文件可以知道数据字典表的定义. 在sql.bsq里面保存了相关的脚本信息，其中就有dcore.bsq 脚本，在该脚本里就可以找到obj$ 表的定义SQL:

/* Formatted on 2011/7/21 14:42:51(QP5 v5.163.1008.3004) */

CREATE TABLE obj$ /*object table */

(

obj# NUMBER NOT NULL, /*object number */

dataobj# NUMBER, /*data layer object number */

owner# NUMBER NOT NULL, /*owner user number */

name VARCHAR2 ("M_IDEN") not null, /* object name */

namespace number not null, /* namespace of object (see KQD.H):*/

/* 1 = TABLE/PROCEDURE/TYPE, 2 =BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */

/* 8 =LOB, 9 = DIRECTORY, */

/* 10 = QUEUE, 11 = REPLICATIONOBJECT GROUP, 12 = REPLICATION PROPAGATOR, */

/* 13 = JAVA SOURCE, 14 = JAVARESOURCE */

/* 58= (Data Mining) MODEL */

subname varchar2("M_IDEN"), /* subordinate tothe name */

type# number not null, /* object type(see KQD.H): */

/* 1 =INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */

/* 7 =PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */

/* 11= PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */

/* 19= TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */

/* 23= DIRECTORY , 24 = QUEUE, */

/* 25= IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */

/* 28= JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */

/* 32= INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */

/* 35= INDEX SUBPARTITION */

/* 82= (Data Mining) MODEL */

/* 92= OLAP CUBE DIMENSION, 93 = OLAP CUBE */

/* 94= OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */

ctime date not null, /*object creation time */

mtime date not null, /* DDLmodification time */

stime date not null, /* specificationtimestamp (version) */

status number not null, /* status ofobject (see KQD.H): */

/* 1 =VALID/AUTHORIZED WITHOUT ERRORS, */

/* 2 =VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */

/* 3 =VALID/AUTHORIZED WITH COMPILATION ERRORS, */

/* 4 =VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */

remoteowner varchar2("M_IDEN"), /* remote ownername (remote object) */

linkname varchar2("M_XDBI"), /* link name(remote object) */

flags number, /* 0x01 = extentmap checking required */

/*0x02 = temporary object */

/*0x04 = system generated object */

/*0x08 = unbound (invoker's rights) */

/*0x10 = secondary object */

/*0x20 = in-memory temp table */

/*0x80 = dropped table (RecycleBin) */

/*0x100 = synonym VPD policies */

/*0x200 = synonym VPD groups */

/*0x400 = synonym VPD context */

/*0x4000 = nested table partition */

oid$ raw(16), /* OID for typedtable, typed view, and type */

spare1 number, /* sqlversion flag: see kpul.h */

spare2 number, /*object version number */

spare3 number, /*base user# */

spare4 varchar2(1000),

spare5 varchar2(1000),

spare6 date

)

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

在obj$ 这个字典里面，保存的是namespace的ID。他们之间的对应关系在obj$里有说明。

namespace number not null, /* namespace ofobject (see KQD.H): */

1 = TABLE/PROCEDURE/TYPE,

2 = BODY,

3 = TRIGGER,

4 = INDEX,

5 = CLUSTER,

8 = LOB,

9 = DIRECTORY,

10 = QUEUE,

11 = REPLICATION OBJECT GROUP,

12 = REPLICATION PROPAGATOR,

13 = JAVA SOURCE,

14 = JAVA RESOURCE

58 = (Data Mining) MODEL

查看obj$中共有多少个不同的namespace：

SYS@anqing2(rac2)> select distinctnamespace from obj$ order by namespace;

NAMESPACE

----------

17 rows selected.

SYS@anqing2(rac2)> select distinct namespace from v$librarycache;

NAMESPACE

---------------

BODY

JAVA DATA

SQL AREA

OBJECT

PIPE

JAVA RESOURCE

TABLE/PROCEDURE

TRIGGER

INDEX

JAVA SOURCE

CLUSTER

11 rows selected.

注意：

在这里有一个问题，我们通过distinct obj$ 和distinct v$librarycache 查看的值不对应，而且这个值与我们obj$的注释也不一致。这个问题是关键，因为它可以引出到底Namespace 是什么。

看一下DSI405里对library cache object所属于的namespace的定义：

1.Library cache objects are grouped in namespaces according to their type.

2.Each object can only be of one type.

3.All the objects of the same type are in the same namespace.

4.A namespace may be used by more than one type.

5. The most important namespace is called cursor (CRSR)and houses the shared SQL cursors.

通过这段解释，我们可以看出，我们之前通过obj$ 看到的namespace 是不全的，因为像shared cursor这样的library cache object根本就不在obj$里。

可以这样来理解Namespace：

Namespace是针对缓存在library cache里的library cache object来说的。

我们之前在obj$里也有namespace的定义，是因为library cache object有一部分的来源就是来自于数据库里已经存在的、固化的object的metadata。

在DSI405中关于library cache object所属于的namespace的详细说明：

Currentlythere are 64 different object types but this number may grow at any time withthe introduction of new features. Examples of types are: cursor, table,synonym, sequence, index, LOB, Java source, outline, dimension, and so on. Not every type corresponds to a namespace. Actually, thereare only 32 namespaces which, of course, are also subject to increase at anytime.

Youcan see the list of namespaces in the library cache dump.

Whatis a certainty is that all the objects of the same type will always be storedin the same namespace. An object can only be of one type, hence the search foran object in the library cache is reduced to a search for this object in thecorresponding namespace.

Somenamespaces contain objects of two or three different types.

These are some of themost commonly used namespaces:

CRSR: Stores library objects of type cursor (sharedSQL statements)

TABL/PRCD/TYPE: Stores tables, views, sequences,synonyms, procedure specifications, function specifications, packagespecifications, libraries, and type specifications

BODY/TYBD: Stores procedure, function, package, and typebodies

TRGR: Stores library objects of type trigger

INDX: Stores library objects of type index

CLST: Stores library objects of type cluster

The exact number and name of namespaces in use depends on theserver features that are used by the application. For example, if theapplication uses Java, namespaces like JVSC (Java source) and JVRE (Javaresource) may be used, otherwise they will not be used.

Note: These namespaces do not store tables, clusters, orindexes as such, only the metadata is stored.

在obj$的注释里提到了KQD.H文件，这个是Oracle 的源代码，我们看不到它的内容，虽然这里面有我们所有namespace 和其对应的namespace id。不过我们可以dump library cache，来查看该版本下所有namespace 的名称。版本不同，namespace 也可能不同。

--查看Oracle 版本

SYS@anqing2(rac2)> select * fromv$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Prod

SYS@anqing2(rac2)> oradebug setmypid

Statement processed.

--把librarycache dump 出来

SYS@anqing2(rac2)> alter session set events 'immediate trace name library_cachelevel 1';

Session altered.

--获取tracefile 名称和路径

SYS@anqing2(rac2)> oradebug tracefile_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.trc

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Production

With the Partitioning, Real ApplicationClusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1

System name: Linux

Node name: rac2

Release: 2.6.18-194.el5

Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010

Machine: i686

Instance name: anqing2

Redo thread mounted by this instance: 2

Oracle process number: 23

Unix process pid: 18783, image: oracle@rac2(TNS V1-V3)

*** 2011-07-21 19:23:32.578

*** ACTION NAME:() 2011-07-21 19:23:32.574

*** MODULE NAME:(sqlplus@rac2 (TNS V1-V3))2011-07-21 19:23:32.574

*** SERVICE NAME:(SYS$USERS) 2011-07-2119:23:32.574

*** SESSION ID:(128.4091) 2011-07-2119:23:32.574

LIBRARY CACHE STATISTICS:

namespace gets hit ratio pins hit ratio reloads invalids

-------------- --------- ------------------ --------- ---------- ----------

CRSR 164017 0.978 4693745 0.999 373 123

TABL 62538 0.966 1408421 0.868 182957 0

BODY 1575 0.970 1937 0.967 8 0

TRGR 231 0.818 284 0.852 0 0

INDX 60 0.183 107 0.430 4 0

CLST 281 0.954 720 0.982 0 0

KGLT 0 0.000 0 0.000 0 0

PIPE 0 0.000 0 0.000 0 0

LOB 0 0.000 0 0.000 0 0

DIR 2 0.500 4 0.500 0 0

QUEU 3 0.000 13 0.462 1 0

OBJG 0 0.000 0 0.000 0 0

PROP 0 0.000 0 0.000 0 0

JVSC 0 0.000 0 0.000 0 0

JVRE 0 0.000 0 0.000 0 0

ROBJ 0 0.000 0 0.000 0 0

REIP 0 0.000 0 0.000 0 0

CPOB 0 0.000 0 0.000 0 0

EVNT 15773 1.000 15915 0.999 1 0

SUMM 0 0.000 0 0.000 0 0

DIMN 0 0.000 0 0.000 0 0

CTX 0 0.000 0 0.000 0 0

OUTL 0 0.000 0 0.000 0 0

RULS 1 0.000 3 0.667 0 0

RMGR 32159 1.000 39619 1.000 1 0

XDBS 7 0.286 7 0.000 0 0

PPLN 0 0.000 0 0.000 0 0

PCLS 0 0.000 0 0.000 0 0

SUBS 2 0.500 2 0.500 0 0

LOCS 0 0.000 0 0.000 0 0

RMOB 0 0.000 0 0.000 0 0

RSMD 0 0.000 0 0.000 0 0

JVSD 0 0.000 0 0.000 0 0

STFG 0 0.000 0 0.000 0 0

TRANS 0 0.000 0 0.000 0 0

RELC 0 0.000 0 0.000 0 0

RULE 0 0.000 0 0.000 0 0

STRM 0 0.000 0 0.000 0 0

REVC 1 0.000 0 0.000 0 0

STAP 0 0.000 0 0.000 0 0

RELS 0 0.000 0 0.000 0 0

RELD 0 0.000 0 0.000 0 0

IFSD 0 0.000 0 0.000 0 0

XDBC 1 0.000 1 0.000 0 0

USAG 0 0.000 0 0.000 0 0

MVOBTBL 1 0.000 1 0.000 0 0

JSQI 0 0.000 0 0.000 0 0

CDC 0 0.000 0 0.000 0 0

MVOBIND 1 0.000 1 0.000 0 0

STBO 0 0.000 0 0.000 0 0

HTSO 0 0.000 0 0.000 0 0

JSGA 9805 0.999 3152085 0.942 181763 0

JSET 7 0.000 7 0.000 0 0

TABL_T 20 0.400 20 0.000 8 0

CLST_T 0 0.000 0 0.000 0 0

INDX_T 2 0.000 2 0.000 0 0

NSCPD 0 0.000 0 0.000 0 0

JSLV 4 0.750 4 0.750 0 0

MODL 0 0.000 0 0.000 0 0

CUMULATIVE 286491 0.979 9312898 0.960 365116 123

第一列就是Oracle 10.2.0.4.0 版本下所有Namespace 的个数：共60个。这个远比我们通过obj$ 查询出来的多。

三. Oracle 11g 中dba_objects中的Namespace

在Oracle 11g里面，dba_objects 视图里也添加了Namespace 的字段，从而帮助我们查询。如SQL:

/* Formatted on 2011/7/21 14:33:09(QP5 v5.163.1008.3004) */

SELECTobject_type,namespace, COUNT (*)

FROMdba_objects

GROUP BYobject_type,namespace

ORDER BYnamespace;

我们可以通过如下语句来查看哪些表里都还有Namespace 字段：

/* Formatted on 2011/7/21 14:34:37(QP5 v5.163.1008.3004) */

SELECT *

FROMdict_columns

WHERE column_name = 'NAMESPACE';

TABLE_NAME COLUMN_NAME COMMENTS

------------------------ ------------------------------------------------------

ALL_CONTEXT NAMESPACE Namespace of the active context

DBA_CONTEXT NAMESPACE Namespace of the context

DBA_GLOBAL_CONTEXT NAMESPACE

DBA_POLICY_CONTEXTS NAMESPACE Namespace of the context

ALL_POLICY_CONTEXTS NAMESPACE Namespace of the context

USER_POLICY_CONTEXTS NAMESPACE Namespace of the context

DBA_REGISTRY NAMESPACE

USER_REGISTRY NAMESPACE

DBA_REGISTRY_HIERARCHY NAMESPACE

DBA_REGISTRY_LOG NAMESPACE

DBA_REGISTRY_HISTORY NAMESPACE

DBA_HIST_LIBRARYCACHE NAMESPACE

V$LIBRARYCACHE NAMESPACE

V$DB_OBJECT_CACHE NAMESPACE

GV$LIBRARYCACHE NAMESPACE

GV$DB_OBJECT_CACHE NAMESPACE

V$CONTEXT NAMESPACE

GV$CONTEXT NAMESPACE

V$GLOBALCONTEXT NAMESPACE

GV$GLOBALCONTEXT NAMESPACE

20 rows selected.

--这个是oracle10g的一个查询结果，在Oracle 11g里面，查询结果有35个。

致谢：

非常感谢dbsnake 在这个问题上对我指导。解除了我在这个问题上的一些疑惑。

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天群：40132017 聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请

posted @ 2011-07-21 20:25 生活不是用来挥霍的阅读(436) 评论(0) 编辑收藏举报

生活不是用来挥霍的

Oracle Namespace 说明

一. 初识Namespace

二. 深入研究Namespace

注意：

三. Oracle 11g 中dba_objects中的Namespace

致谢：

公告