oracle object_type,oracle817视图中object_type的'Undefined'

在oracle8i中，可以在***_objects视图中看到对象类型object_type为“UNDEFINED”的纪录。这通常是由materialized views/snapshot 引起的。

SQL> set long 5000SQL> 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'),--缺少对象类型为42的物化视图       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$ uwhere 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 allselect 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$ uwhere l.owner# = u.user#正确的视图定义如下

SQL> select text from dba_views 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'), --  没有定义的默认设置       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#

可以看到，视图定义中decode函数将不存在的o.type#都归入为'UNDEFINED' ；如果必要，可以自己定义正确的视图；该问题在高版本已经解决