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 版本不同，可能有出入。

1 2 3 下一頁