一. 初识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,我们可以查看他们之间的对应关系:
SELECT username,user_id
FROM dba_users
WHERE user_id IN (SELECT DISTINCT owner# FROM obj$);
USERNAME USER_ID------------------------------ ----------
OOP 64
QXW 74
NOP 66
AOP 65
MONITOR 75
WURUNDUO 79
ORACLE_OCM 21
DBSNMP 30
SYSMAN 76
FLOWS_FILES 60
MDSYS 55
USERNAME USER_ID
------------------------------ ----------
ORDSYS 51
EXFSYS 42
WMSYS 32
APPQOSSYS 31
APEX_030200 63
ORDDATA 52
XDB 43
ORDPLUGINS 53
SI_INFORMTN_SCHEMA 54
SYS 0
SYSTEM 5
USERNAME USER_ID
------------------------------ ----------
OUTLN 9
以下类型的对象使用同一个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 对应名称和它所包含的对象的个数:
SELECT namespace, object_type, COUNT(*)
FROM (SELECT namespace,
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 by namespace, object_type;
NAMESPACE OBJECT_TYPE COUNT(*)
---------- ----------------- ----------
4 INDEX 4706
32 JAVADATA 325
2 TYPEBODY 222
21 CONTEXT 6
9 DIRECTORY 5
51 UNDEFINED 11
1 NEXTOBJECT 1
13 JAVASOURCE 2
19 MATERIALIZEDVIEW 1
1 VIEW 4679
1 PACKAGE 1139
24 CONSUMERGROUP 25
64 SECURITYPROFILE 1
1 SYNONYM 27122
1 PROCEDURE 196
3 TRIGGER 547
14 JAVARESOURCE 837
1 SEQUENCE 267
24 RESOURCEPLAN 10
8 LOBPARTITION 1
NAMESPACE OBJECT_TYPE COUNT(*)
---------- ----------------- ----------
1 TABLESUBPARTITION 32
1 INDEXTYPE 5
1 JAVACLASS 22659
1 TYPE 2742
38 EVALUATIONCONTEXT 13
4 INDEX PARTITION 386
1 TABLE 2925
2 PACKAGEBODY 1095
10 QUEUE 36
8 LOB 940
23 RULESET 19
36 RULE 1
1 FUNCTION 297
1 UNDEFINED 65
1 TABLEPARTITION 400
1 LIBRARY 175
25 XMLSCHEMA 52
5 CLUSTER 10
1 OPERATOR 49
39 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:
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