ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【视图】oracle 数据字典视图之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)

【视图】oracle 数据字典视图之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)

原创 Linux操作系统 作者:secooler 时间:2009-03-18 15:53:23 0 删除 编辑
1. DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)视图是非常非常常用的数据视图,可以获得数据库中任意的对象
sys@ora10g> desc dba_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------------
 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)

sys@ora10g> select count(*) from dba_objects;

  COUNT(*)
----------
     11441

sys@ora10g> select count(*) from obj;

  COUNT(*)
----------
      6751

sys@ora10g> select count(*) from user_objects;

  COUNT(*)
----------
      6751

sys@ora10g> select count(*) from all_objects;

  COUNT(*)
----------
     11376

sys@ora10g> conn sec/sec
Connected.

sec@ora10g> select object_name,object_type from obj;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST                           TABLE
STATS_TEST                     TABLE

2.通过查看catalog.sql获得oracle创建DBA_OBJECTS数据字典视图的语句
create or replace view DBA_OBJECTS
    (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
     OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
     TEMPORARY, GENERATED, SECONDARY)
as
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
/

3.oracle官方文档中关于ALL_OBJECTS的描述

ALL_OBJECTS

ALL_OBJECTS describes all objects accessible to the current user.

Related Views

  • DBA_OBJECTS describes all objects in the database.

  • USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.

Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Owner of the object
OBJECT_NAME VARCHAR2(30) NOT NULL Name of the object
SUBOBJECT_NAME VARCHAR2(30)
Name of the subobject (for example, partition)
OBJECT_ID NUMBER NOT NULL Dictionary object number of the object
DATA_OBJECT_ID NUMBER
Dictionary object number of the segment that contains the object



Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system.
OBJECT_TYPE VARCHAR2(19)
Type of the object (such as TABLE, INDEX)
CREATED DATE NOT NULL Timestamp for the creation of the object
LAST_DDL_TIME DATE NOT NULL Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)
TIMESTAMP VARCHAR2(20)
Timestamp for the specification of the object (character data)
STATUS VARCHAR2(7)
Status of the object (VALID, INVALID, or N/A)
TEMPORARY VARCHAR2(1)
Whether the object is temporary (the current session can see only data that it placed in this object itself)
GENERATED VARCHAR2(1)
Indicates whether the name of this object was system generated (Y) or not (N)
SECONDARY VARCHAR2(1)
Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N)

4.小结
数据库中包含数以万计的对象, DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)这些视图就像是一个小爬犁,通过这些视图可以很快的了解某个SCHEMA包含的内容。

Good luck.

secooler
09.03.18

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-571440/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8023060