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,我们可以查看他们之间的对应关系:

SELECT username,user_id

  FROM dba_users

WHERE user_id IN (SELECT DISTINCT owner# FROM obj$);

USERNAME                          USER_ID
------------------------------ ----------
OOP                                    64
QXW                                    74
NOP                                    66
AOP                                    65
MONITOR                                75
WURUNDUO                               79
ORACLE_OCM                             21
DBSNMP                                 30
SYSMAN                                 76
FLOWS_FILES                            60
MDSYS                                  55


USERNAME                          USER_ID
------------------------------ ----------
ORDSYS                                 51
EXFSYS                                 42
WMSYS                                  32
APPQOSSYS                              31
APEX_030200                            63
ORDDATA                                52
XDB                                    43
ORDPLUGINS                             53
SI_INFORMTN_SCHEMA                     54
SYS                                     0
SYSTEM                                  5


USERNAME                          USER_ID
------------------------------ ----------
OUTLN                                   9

以下类型的对象使用同一个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 对应名称和它所包含的对象的个数:


SELECT namespace, object_type, COUNT(*)
  FROM (SELECT namespace,
                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 by namespace, object_type;

 NAMESPACE OBJECT_TYPE         COUNT(*)
---------- ----------------- ----------
         4 INDEX                   4706
        32 JAVADATA                 325
         2 TYPEBODY                 222
        21 CONTEXT                    6
         9 DIRECTORY                  5
        51 UNDEFINED                 11
         1 NEXTOBJECT                 1
        13 JAVASOURCE                 2
        19 MATERIALIZEDVIEW           1
         1 VIEW                    4679
         1 PACKAGE                 1139
        24 CONSUMERGROUP             25
        64 SECURITYPROFILE            1
         1 SYNONYM                27122
         1 PROCEDURE                196
         3 TRIGGER                  547
        14 JAVARESOURCE             837
         1 SEQUENCE                 267
        24 RESOURCEPLAN              10
         8 LOBPARTITION               1
 
 NAMESPACE OBJECT_TYPE         COUNT(*)
---------- ----------------- ----------
         1 TABLESUBPARTITION         32
         1 INDEXTYPE                  5
         1 JAVACLASS              22659
         1 TYPE                    2742
        38 EVALUATIONCONTEXT         13
         4 INDEX PARTITION          386
         1 TABLE                   2925
         2 PACKAGEBODY             1095
        10 QUEUE                     36
         8 LOB                      940
        23 RULESET                   19
        36 RULE                       1
         1 FUNCTION                 297
         1 UNDEFINED                 65
         1 TABLEPARTITION           400
         1 LIBRARY                  175
        25 XMLSCHEMA                 52
         5 CLUSTER                   10
         1 OPERATOR                  49
 
39 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:

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


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值