RE: obj$ table -- type column

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 20 Feb 2001 13:31:24 -0800
Message-ID: <F001.002B8F8A.20010220122050@fatcity.com>

> -----Original Message-----
> From: Pablo ksksksk [mailto:p_rodri99_at_yahoo.es]
> Sent: mardi, 20. février 2001 11:50
> 
> what does represent the column "type=11" and "type=0"
> from obj$ table?

Look at the dba_objects view (see below). type = 11 is package body

type = 0 is 'next object'? In my 8.1.6 test database I only see one object with type 0, and the object name is '_NEXT_OBJECT'. Don't know what that is.

SQL> set long 4000
SQL> select text from dba_views where view_name = 'DBA_OBJECTS' ;

TEXT

---

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',

                      39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',

                      43, 'DIMENSION',

                      44, 'CONTEXT', 47, 'RESOURCE PLAN',

                      48, 'CONSUMER GROUP',

                      51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA',

                     '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_'
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#

---

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

 
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Tue Feb 20 2001 - 15:31:24 CST