[20171115]關於namespace.txt

--//第一次聽到這個概念,好像是那篇blog提到,表與索引在不同的namespace裡面.也就是在相同schema下建立的表可以與索引同名.
--//而在同一schema下的namespace的建立的物件名字是不能重名的.

--//前幾天在測試dbms_shared_pool.pin時,發現SEQUENCE的namespace竟然是TABLE/PROCEDURE,感覺有必要做一些學習,瞭解這方面
--//的知識.

--//摘要: http://blog.csdn.net/tianlesoftware/article/details/6624122

Oracle透過namespace來管理schema object的名字，關於Namespace 的定義，在官網文件上沒有找到一個詳細的定義，在網上搜到一些相
關資訊：

Schema Object Namespaces

A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name.
Objects in different namespaces can share the same name.

The Oracle database uses namespaces to resolve schema object references. When you refer to an object in a SQL statement,
Oracle considers the context of the SQL statement and locates the object in the appropriate namespace. After locating
the object, Oracle performs 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 in the 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. This means, for example, that two tables
in different schemas are in different namespaces and can have the same name.

--以上解釋提到了幾點：

1.每個使用者都有自己對應的namespace來儲存自己的物件
2.表和檢視存放在同一個namespace,所以對於同一個使用者的表和檢視不能重名,但是表和索引是存放在不同的namespace，所以可以重名。

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SYS@book> select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1;
KGLHDNSP KGLHDNSD                        KGLOBTYD
-------- ------------------------------- ---------------------------------
       0 SQL AREA                        CURSOR
       1 TABLE/PROCEDURE                 CURSOR
       1 TABLE/PROCEDURE                 FUNCTION
       1 TABLE/PROCEDURE                 LIBRARY
       1 TABLE/PROCEDURE                 OPERATOR
       1 TABLE/PROCEDURE                 PACKAGE
       1 TABLE/PROCEDURE                 PROCEDURE
       1 TABLE/PROCEDURE                 SEQUENCE
       1 TABLE/PROCEDURE                 SYNONYM
       1 TABLE/PROCEDURE                 TABLE
       1 TABLE/PROCEDURE                 TYPE
       1 TABLE/PROCEDURE                 VIEW
       2 BODY                            CURSOR
       2 BODY                            PACKAGE BODY
       2 BODY                            TYPE BODY
       4 INDEX                           INDEX
       5 CLUSTER                         CLUSTER
      10 QUEUE                           QUEUE
      18 PUB SUB INTERNAL INFORMATION    PUB SUB INTERNAL INFORMATION
      23 RULESET                         RULESET
      24 RESOURCE MANAGER                RESOURCE MANAGER CONSUMER GROUP
      45 MULTI-VERSION OBJECT FOR TABLE MULTI-VERSIONED OBJECT
      48 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT
      51 SCHEDULER GLOBAL ATTRIBUTE      CURSOR
      51 SCHEDULER GLOBAL ATTRIBUTE      SCHEDULER GLOBAL ATTRIBUTE
      52 SCHEDULER EARLIEST START TIME   SCHEDULER EARLIEST START TIME
      64 EDITION                         EDITION
      69 DBLINK                          CURSOR
      73 SCHEMA                          CURSOR
      73 SCHEMA                          NONE
      74 DBINSTANCE                      CURSOR
      75 SQL AREA STATS                  CURSOR STATS
      79 ACCOUNT_STATUS                  NONE
      82 SQL AREA BUILD                  CURSOR
34 rows selected.

--//個人感覺查詢比較全,至少我機器正在執行的namespace.

Within a namespace, no two objects can have the same name.

The following schema objects share one namespace:
    Tables
    Views
    Sequences
    Private synonyms
    Stand-alone procedures
    Stand-alone stored functions
    Packages
    Materialized views
    User-defined types

Each of the following schema objects has its own namespace:
    Indexes
    Constraints
    Clusters
    Database triggers
    Private database links
    Dimensions

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name.
However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the
same name.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables
in different schemas are in different namespaces and can have the same name.

Each of the following nonschema objects also has its own namespace:

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

Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.

--//注意看同義詞,Private synonyms與table在一個名字空間裡面,而 Public synonyms是分開單獨的名字空間.
--//這些概念不小心還是亂...

--//繼續做一些測試:

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:

SCOTT@book> CREATE SYNONYM DEPT FOR SCOTT.DEPT;
CREATE SYNONYM DEPT FOR SCOTT.DEPT
*
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object

--//而建立PUBLIC SYNONYM 就沒有問題.

SCOTT@book> CREATE PUBLIC SYNONYM DEPT FOR SCOTT.DEPT;
Synonym created.

SCOTT@book> drop PUBLIC SYNONYM DEPT ;
Synonym dropped.

--//因為這兩者屬於不同的名字空間,這就很好理解.

[20171115]關於namespace.txt

相關文章