在Oracle8i中，可以在***_objects視圖中看到對(duì)象類型object_type為“UNDEFINED”的紀(jì)錄。這通常是由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'),--缺少對(duì)象類型為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'), -- 沒(méi)有定義的默認(rèn)設(shè)置 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函數(shù)將不存在的o.type#都?xì)w入為'UNDEFINED' ；假如必要，可以自己定義正確的視圖；該問(wèn)題在高版本已經(jīng)解決
