ITPub博客

首页 > 数据库 > Oracle > 11g视图dba_objects中增加了1个有用的字段namespace

11g视图dba_objects中增加了1个有用的字段namespace

原创 Oracle 作者:warehouse 时间:2009-10-02 18:31:47 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5097837