The Oracle (tm) Users' Co-Operative FAQ

What is an object of type 'Undefined' ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: March 2002

Oracle version(s): 7.3-9.0

Occasionally when quering the xxx_OBJECTS you will see an object that has a type of 'UNDEFINED'. What is the cause of this?

Back to index of questions


A quick look at the definition for xxx_OBJECTS shows the cause of the anomaly. As new object types are introduced (for example: partitions, nested tables, materialised views etc), the view needs to be updated to reflect the new object type. Occasionally the Oracle developers appear to miss the new object types which thus fall through the DECODE into the "UNDEFINED" tag

The most commonly reported occurrence of this is appears to be materialised views in 8i.

SQL> set long 5000
SQL> select text
  2  from dba_views
  3  where view_name = 'DBA_OBJECTS'
  4  /

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',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE',
                     'UNDEFINED'),				--  THIS IS THE LINE TO LOOK AT
       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#

Further reading: N/A


Back to top

Back to index of questions