Oracle Namespace 說明
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 版本不同,可能有出入。
二. 深入研究Namespace
先執行如下SQL,檢視每個namespace 對應名稱和它所包含的物件的個數:
/* Formatted on 2011/7/21 14:24:47(QP5 v5.163.1008.3004) */
SELECTnamespace,object_type, COUNT (*)
FROM (SELECTnamespace,
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 BYnamespace,object_type;
NAMESPACE OBJECT_TYPE COUNT(*)
---------- ------------------ ----------
4 INDEX 2253
2 TYPE BODY 175
51 UNDEFINED 6
21 CONTEXT 5
9 DIRECTORY 6
1 SYNONYM 20122
1 PACKAGE 859
1 VIEW 3684
1 PROCEDURE 98
3 TRIGGER 164
38 EVALUATION CONTEXT 12
1 SEQUENCE 138
2 PACKAGE BODY 804
1 INDEXTYPE 10
1 TYPE 1953
24 RESOURCE PLAN 3
4 INDEX PARTITION 144
25 XML SCHEMA 25
1 TABLE 1619
8 LOB 541
10 QUEUE 23
23 RULE SET 15
8 LOB PARTITION 1
36 RULE 4
1 JAVA CLASS 16450
1 NEXT OBJECT 1
1 FUNCTION 268
1 UNDEFINED 660
1 LIBRARY 154
32 JAVA DATA 298
5 CLUSTER 10
24 CONSUMER GROUP 5
1 TABLE PARTITION 124
1 OPERATOR 57
14 JAVA RESOURCE 775
35 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:
/* Formatted on 2011/7/21 14:42:51(QP5 v5.163.1008.3004) */
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
檢視obj$中共有多少個不同的namespace:
SYS@anqing2(rac2)> select distinctnamespace from obj$ order by namespace;
NAMESPACE
----------
1
2
3
4
5
8
9
10
14
21
23
24
25
32
36
38
51
17 rows selected.
SYS@anqing2(rac2)> select distinct namespace from v$librarycache;
NAMESPACE
---------------
BODY
JAVA DATA
SQL AREA
OBJECT
PIPE
JAVA RESOURCE
TABLE/PROCEDURE
TRIGGER
INDEX
JAVA SOURCE
CLUSTER
11 rows selected.
注意:
在這裡有一個問題,我們透過distinct obj$ 和distinct v$librarycache 檢視的值不對應,而且這個值與我們obj$的註釋也不一致。 這個問題是關鍵,因為它可以引出到底Namespace 是什麼。
看一下DSI405裡對library cache object所屬於的namespace的定義:
1.Library cache objects are grouped in namespaces according to their type.
2.Each object can only be of one type.
3.All the objects of the same type are in the same namespace.
4.A namespace may be used by more than one type.
5. The most important namespace is called cursor (CRSR)and houses the shared SQL cursors.
透過這段解釋,我們可以看出,我們之前透過obj$ 看到的namespace 是不全的,因為像shared cursor這樣的library cache object根本就不在obj$裡。
可以這樣來理解Namespace:
Namespace是針對快取在library cache裡的library cache object來說的。
我們之前在obj$裡也有namespace的定義,是因為library cache object有一部分的來源就是來自於資料庫裡已經存在的、固化的object的metadata。
在DSI405中關於library cache object所屬於的namespace的詳細說明:
Currentlythere are 64 different object types but this number may grow at any time withthe introduction of new features. Examples of types are: cursor, table,synonym, sequence, index, LOB, Java source, outline, dimension, and so on. Not every type corresponds to a namespace. Actually, thereare only 32 namespaces which, of course, are also subject to increase at anytime.
Youcan see the list of namespaces in the library cache dump.
Whatis a certainty is that all the objects of the same type will always be storedin the same namespace. An object can only be of one type, hence the search foran object in the library cache is reduced to a search for this object in thecorresponding namespace.
Somenamespaces contain objects of two or three different types.
These are some of themost commonly used namespaces:
CRSR: Stores library objects of type cursor (sharedSQL statements)
TABL/PRCD/TYPE: Stores tables, views, sequences,synonyms, procedure specifications, function specifications, packagespecifications, libraries, and type specifications
BODY/TYBD: Stores procedure, function, package, and typebodies
TRGR: Stores library objects of type trigger
INDX: Stores library objects of type index
CLST: Stores library objects of type cluster
The exact number and name of namespaces in use depends on theserver features that are used by the application. For example, if theapplication uses Java, namespaces like JVSC (Java source) and JVRE (Javaresource) may be used, otherwise they will not be used.
Note: These namespaces do not store tables, clusters, orindexes as such, only the metadata is stored.
在obj$的註釋裡提到了KQD.H檔案,這個是Oracle 的原始碼,我們看不到它的內容,雖然這裡面有我們所有namespace 和其對應的namespace id。 不過我們可以dump library cache,來檢視該版本下所有namespace 的名稱。 版本不同,namespace 也可能不同。
--檢視Oracle 版本
SYS@anqing2(rac2)> select * fromv$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Prod
SYS@anqing2(rac2)> oradebug setmypid
Statement processed.
--把librarycache dump 出來
SYS@anqing2(rac2)> alter session set events 'immediate trace name library_cachelevel 1';
Session altered.
--獲取tracefile 名稱和路徑
SYS@anqing2(rac2)> oradebug tracefile_name
/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trc
[oracle@rac2 ~]$ cat/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trc
/u01/app/oracle/admin/anqing/udump/anqing2_ora_18783.trc
Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: rac2
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine: i686
Instance name: anqing2
Redo thread mounted by this instance: 2
Oracle process number: 23
Unix process pid: 18783, image: oracle@rac2(TNS V1-V3)
*** 2011-07-21 19:23:32.578
*** ACTION NAME:() 2011-07-21 19:23:32.574
*** MODULE NAME:(sqlplus@rac2 (TNS V1-V3))2011-07-21 19:23:32.574
*** SERVICE NAME:(SYS$USERS) 2011-07-2119:23:32.574
*** SESSION ID:(128.4091) 2011-07-2119:23:32.574
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- ------------------ --------- ---------- ----------
CRSR 164017 0.978 4693745 0.999 373 123
TABL 62538 0.966 1408421 0.868 182957 0
BODY 1575 0.970 1937 0.967 8 0
TRGR 231 0.818 284 0.852 0 0
INDX 60 0.183 107 0.430 4 0
CLST 281 0.954 720 0.982 0 0
KGLT 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIR 2 0.500 4 0.500 0 0
QUEU 3 0.000 13 0.462 1 0
OBJG 0 0.000 0 0.000 0 0
PROP 0 0.000 0 0.000 0 0
JVSC 0 0.000 0 0.000 0 0
JVRE 0 0.000 0 0.000 0 0
ROBJ 0 0.000 0 0.000 0 0
REIP 0 0.000 0 0.000 0 0
CPOB 0 0.000 0 0.000 0 0
EVNT 15773 1.000 15915 0.999 1 0
SUMM 0 0.000 0 0.000 0 0
DIMN 0 0.000 0 0.000 0 0
CTX 0 0.000 0 0.000 0 0
OUTL 0 0.000 0 0.000 0 0
RULS 1 0.000 3 0.667 0 0
RMGR 32159 1.000 39619 1.000 1 0
XDBS 7 0.286 7 0.000 0 0
PPLN 0 0.000 0 0.000 0 0
PCLS 0 0.000 0 0.000 0 0
SUBS 2 0.500 2 0.500 0 0
LOCS 0 0.000 0 0.000 0 0
RMOB 0 0.000 0 0.000 0 0
RSMD 0 0.000 0 0.000 0 0
JVSD 0 0.000 0 0.000 0 0
STFG 0 0.000 0 0.000 0 0
TRANS 0 0.000 0 0.000 0 0
RELC 0 0.000 0 0.000 0 0
RULE 0 0.000 0 0.000 0 0
STRM 0 0.000 0 0.000 0 0
REVC 1 0.000 0 0.000 0 0
STAP 0 0.000 0 0.000 0 0
RELS 0 0.000 0 0.000 0 0
RELD 0 0.000 0 0.000 0 0
IFSD 0 0.000 0 0.000 0 0
XDBC 1 0.000 1 0.000 0 0
USAG 0 0.000 0 0.000 0 0
MVOBTBL 1 0.000 1 0.000 0 0
JSQI 0 0.000 0 0.000 0 0
CDC 0 0.000 0 0.000 0 0
MVOBIND 1 0.000 1 0.000 0 0
STBO 0 0.000 0 0.000 0 0
HTSO 0 0.000 0 0.000 0 0
JSGA 9805 0.999 3152085 0.942 181763 0
JSET 7 0.000 7 0.000 0 0
TABL_T 20 0.400 20 0.000 8 0
CLST_T 0 0.000 0 0.000 0 0
INDX_T 2 0.000 2 0.000 0 0
NSCPD 0 0.000 0 0.000 0 0
JSLV 4 0.750 4 0.750 0 0
MODL 0 0.000 0 0.000 0 0
CUMULATIVE 286491 0.979 9312898 0.960 365116 123
第一列就是Oracle 10.2.0.4.0 版本下所有Namespace 的個數:共60個。 這個遠比我們透過obj$ 查詢出來的多。
三. Oracle 11g 中dba_objects中的Namespace
在Oracle 11g裡面,dba_objects 檢視裡也新增了Namespace 的欄位,從而幫助我們查詢。如SQL:
/* Formatted on 2011/7/21 14:33:09(QP5 v5.163.1008.3004) */
SELECTobject_type,namespace, COUNT (*)
FROMdba_objects
GROUP BYobject_type,namespace
ORDER BYnamespace;
我們可以透過如下語句來檢視哪些表裡都還有Namespace 欄位:
/* Formatted on 2011/7/21 14:34:37(QP5 v5.163.1008.3004) */
SELECT *
FROMdict_columns
WHERE column_name = 'NAMESPACE';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------ ------------------------------------------------------
ALL_CONTEXT NAMESPACE Namespace of the active context
DBA_CONTEXT NAMESPACE Namespace of the context
DBA_GLOBAL_CONTEXT NAMESPACE
DBA_POLICY_CONTEXTS NAMESPACE Namespace of the context
ALL_POLICY_CONTEXTS NAMESPACE Namespace of the context
USER_POLICY_CONTEXTS NAMESPACE Namespace of the context
DBA_REGISTRY NAMESPACE
USER_REGISTRY NAMESPACE
DBA_REGISTRY_HIERARCHY NAMESPACE
DBA_REGISTRY_LOG NAMESPACE
DBA_REGISTRY_HISTORY NAMESPACE
DBA_HIST_LIBRARYCACHE NAMESPACE
V$LIBRARYCACHE NAMESPACE
V$DB_OBJECT_CACHE NAMESPACE
GV$LIBRARYCACHE NAMESPACE
GV$DB_OBJECT_CACHE NAMESPACE
V$CONTEXT NAMESPACE
GV$CONTEXT NAMESPACE
V$GLOBALCONTEXT NAMESPACE
GV$GLOBALCONTEXT NAMESPACE
20 rows selected.
--這個是oracle10g的一個查詢結果,在Oracle 11g裡面,查詢結果有35個。
致謝:
非常感謝dbsnake 在這個問題上對我指導。 解除了我在這個問題上的一些疑惑。
About Me
...............................................................................................................................
● 本文轉載自:http://blog.csdn.net/tianlesoftware/article/details/6624122
● 本文在itpub(http://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址:http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群:230161599 微信群:私聊
● 聯絡我請加QQ好友(646634621),註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解
● 版權所有,歡迎分享本文,轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號:xiaomaimiaolhr,掃描右邊的二維碼加入小麥苗的QQ群,學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2141869/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 再說swift namespaceSwiftnamespace
- Oracle Latch 說明Oracle
- Oracle 版本說明Oracle
- oracle orapwd使用說明Oracle
- Oracle BBED 工具 說明Oracle
- ORACLE event和說明Oracle
- Oracle BBED 工具說明Oracle
- Oracle Logminer 說明Oracle
- (轉)Oracle Logminer 說明Oracle
- Oracle golden gate程式說明OracleGo
- Oracle 後臺程式 說明Oracle
- Oracle alter index rebuild 說明OracleIndexRebuild
- Oracle Audit 審計 說明Oracle
- Oracle rdba和 dba 說明Oracle
- oracle引數說明(zt)Oracle
- 【ROWID】Oracle rowid說明Oracle
- Oracle Table建立引數說明Oracle
- Oracle 官方文件 結構說明Oracle
- Oracle Table 建立引數 說明Oracle
- Oracle官方文件結構說明Oracle
- Oracle audit 審計功能說明Oracle
- ORACLE 帳戶 狀態說明Oracle
- (轉)oracle dump block格式說明OracleBloC
- Oracle常見等待事件說明Oracle事件
- Oracle Sequence Cache 引數說明Oracle
- Oracle RAC Past Image(PI) 說明OracleAST
- Oracle DUL/AUL/ODU 工具說明Oracle
- Oracle靜默安裝說明Oracle
- Oracle OS Watcher使用說明Oracle
- Oracle內建事件的說明Oracle事件
- 【ORACLE】Oracle常用SQL及重點功能說明OracleSQL
- RU 和 RUR oracle補丁說明Oracle
- oracle常見的等待事件說明Oracle事件
- Oracle oradebug 命令 使用說明Oracle
- ORACLE執行計劃 explain說明OracleAI
- oracle控制檔案轉儲說明Oracle
- oracle資料塊轉儲說明Oracle
- oracle 常用檢視 簡短說明Oracle