11g檢視dba_objects中增加了1個有用的欄位namespace

warehouse發表於2009-10-02
oracle透過namespace來管理schema object的名字,什麼是namespace可用透過下面的doc大致來了解[@more@]

Schema Object Namespaces
The following have their
own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database
links
• Dimensions

--=============================
The following are in the
same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone
procedures
• Stand-alone stored
functions
• Packages
• Materialized views
• User-defined types

--===============================

Schema Object Namespaces
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 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.

--===============================

在11gR1之前我們是無法透過檢視dba_objects來確定某一個物件究竟在哪一個namespace裡,現在dba_objects中有了namespace可以很容易的知道那類物件和那類物件在同一個名稱空間裡,在同一個名稱空間裡的物件不能重名。當然這個欄位namespace最終還是來自基表obj$

SQL> desc dba_objects
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

SQL> select object_type,namespace,count(*) from dba_objects
2 group by object_type,namespace
3 order by namespace;

OBJECT_TYPE NAMESPACE COUNT(*)
------------------- ---------- ----------
FUNCTION 1 100
INDEXTYPE 1 1
JOB 1 8
JOB CLASS 1 12
LIBRARY 1 136
OPERATOR 1 16
PACKAGE 1 757
PROCEDURE 1 68
PROGRAM 1 18
SCHEDULE 1 2
SEQUENCE 1 136

OBJECT_TYPE NAMESPACE COUNT(*)
------------------- ---------- ----------
SYNONYM 1 3375
TABLE 1 1750
TABLE PARTITION 1 106
TYPE 1 1796
VIEW 1 3940
WINDOW 1 9
WINDOW GROUP 1 4
PACKAGE BODY 2 734
TYPE BODY 2 145
TRIGGER 3 100
INDEX 4 2047

OBJECT_TYPE NAMESPACE COUNT(*)
------------------- ---------- ----------
INDEX PARTITION 4 122
CLUSTER 5 10
LOB 8 230
LOB PARTITION 8 1
DIRECTORY 9 4
QUEUE 10 33
MATERIALIZED VIEW 19 1
CONTEXT 21 4
RULE SET 23 17
CONSUMER GROUP 24 14
RESOURCE PLAN 24 7

OBJECT_TYPE NAMESPACE COUNT(*)
------------------- ---------- ----------
RULE 36 1
EVALUATION CONTEXT 38 11
UNDEFINED 51 6
EDITION 64 1
DATABASE LINK 1

已選擇38行。

--===========================

下面這些字典中都含有namespace欄位,大家可以仔細體會namespace的作用:

SQL> select * from dict_columns where column_name='NAMESPACE';

TABLE_NAME COLUMN_NAM COMMENTS
------------------------------ ---------- ------------------------------
ALL_CONTEXT NAMESPACE Namespace of the active contex
t

DBA_OBJECTS NAMESPACE Namespace for the object
DBA_OBJECTS_AE NAMESPACE Namespace for the object
DBA_POLICY_CONTEXTS NAMESPACE Namespace of the context
DBA_REGISTRY NAMESPACE
DBA_REGISTRY_DEPENDENCIES NAMESPACE
DBA_REGISTRY_HIERARCHY NAMESPACE
DBA_REGISTRY_HISTORY NAMESPACE
DBA_REGISTRY_LOG NAMESPACE

TABLE_NAME COLUMN_NAM COMMENTS
------------------------------ ---------- ------------------------------
DBA_REGISTRY_PROGRESS NAMESPACE
USER_POLICY_CONTEXTS NAMESPACE Namespace of the context
USER_REGISTRY NAMESPACE
ALL_OBJECTS NAMESPACE Namespace for the object
ALL_OBJECTS_AE NAMESPACE Namespace for the object
ALL_POLICY_CONTEXTS NAMESPACE Namespace of the context
ALL_PROBE_OBJECTS NAMESPACE
DBA_GLOBAL_CONTEXT NAMESPACE
DBA_HIST_LIBRARYCACHE NAMESPACE
DBA_SUBSCR_REGISTRATIONS NAMESPACE Subscription namespace
DBA_INVALID_OBJECTS NAMESPACE

TABLE_NAME COLUMN_NAM COMMENTS
------------------------------ ---------- ------------------------------
USER_SUBSCR_REGISTRATIONS NAMESPACE Subscription namespace
DBA_CONTEXT NAMESPACE Namespace of the context
USER_OBJECTS NAMESPACE Namespace for the object
USER_OBJECTS_AE NAMESPACE Namespace for the object
V$CONTEXT NAMESPACE
V$DB_OBJECT_CACHE NAMESPACE
V$LIBRARYCACHE NAMESPACE
GV$GLOBALCONTEXT NAMESPACE
GV$LIBRARYCACHE NAMESPACE
GV$RESULT_CACHE_OBJECTS NAMESPACE
V$RESULT_CACHE_OBJECTS NAMESPACE

TABLE_NAME COLUMN_NAM COMMENTS
------------------------------ ---------- ------------------------------
GV$CONTEXT NAMESPACE
GV$DB_OBJECT_CACHE NAMESPACE
V$GLOBALCONTEXT NAMESPACE
OBJ NAMESPACE Namespace for the object

已選擇35行。

SQL>

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19602/viewspace-1027565/,如需轉載,請註明出處,否則將追究法律責任。

相關文章