【檢視】oracle 資料字典檢視之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS（OBJ）

secooler發表於2009-03-18
OracleObject
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章