11g檢視dba_objects中增加了1個有用的欄位namespace
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 11g檢視dba_segments中增加了一個有用的segment_subtype欄位!
- 11G訪問DBA_OBJECTS和V$LOCK檢視時HANG住Object
- 【GP】透過資料字典檢視某個表的欄位
- 強制轉換檢視某個欄位為某個型別的sql型別SQL
- sql server如何檢視欄位註釋?SQLServer
- Oracle系統檢視中address欄位長度與db位數的關係Oracle
- sql2005 獲取表欄位資訊和檢視欄位資訊SQL
- 【ORACLE】物化檢視相關後設資料檢視欄位說明Oracle
- 10g 增加了v$sqlstats 這個動態檢視SQL
- 檢視oracle資料庫中,哪些表的欄位是null值比較多Oracle資料庫Null
- SAP MM 物料主資料採購檢視中的欄位'Var. OUn'的作用?
- oracle檢視該使用者的所有表名字、表註釋、欄位名、欄位註釋、是否為空、欄位型別Oracle型別
- Hibernate對檢視對映,當檢視中有空欄位的時候如何解決?
- sql server 修改欄位名,檢視指定表是否存在SQLServer
- oracle資料庫dba_hist等檢視中的Delta相關欄位介紹Oracle資料庫
- [20171102]檢視v$session中process欄位含義Session
- sql去除某個欄位中的某個字串 replaceSQL字串
- Sql查詢 一個表中某欄位的資料在另一個表中某欄位中不存在的SQL
- 【檢視】oracle 資料字典檢視之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)OracleObject
- 圖解MySQL:count(*) 、count(1) 、count(主鍵欄位)、count(欄位)哪個效能最好?圖解MySql
- 檢視oracle 欄位的資料佔用的位元組數函式Oracle函式
- SQL Server中根據某個欄位,ID欄位自動增長的實現SQLServer
- Oracle中的Rownum 欄位Oracle
- Oracle中的大欄位Oracle
- postgresql中檢視建立,欄位拼接,同一個表的多行之間的多個欄位相減SQL
- MySQL中修改一個資料庫下包含有某個相同欄位的所有表的欄位長度MySql資料庫
- mysql正則匹配解決查詢一個欄位是否在另一個欄位中MySql
- 11g中關於表新增欄位default屬性研究
- SAP RETAIL商品主資料Basic Data檢視裡幾個讓人莫名驚詫的欄位AI
- 如何檢視錶中的二進位制流
- 全面學習MySQL中的檢視(1) 檢視安全驗證的方式MySql
- MySQL如何檢視新增修改表以及欄位註釋資訊MySql
- 二進位制中1的個數
- oracle中lob欄位Oracle
- Dynamics CRM 通過配置來設定查詢欄位的預設檢視
- oracle 11G 新增欄位調整效能Oracle
- 將多個JSON欄位對映到單個Java欄位JSONJava
- [轉]23個最有用的Elasticsearch檢索技巧Elasticsearch