VIEW: SYS.DBA_TYPES

Source

View Text - Preformatted

select u.name, o.name, t.toid,
       decode(t.typecode, 108, 'OBJECT',
                          122, 'COLLECTION',
                          o.name),
       t.attributes, t.methods,
       decode(bitand(t.properties, 16), 16, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 256), 256, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 8), 8, 'NO', 'YES'),
       decode(bitand(t.properties, 65536), 65536, 'NO', 'YES'),
       su.name, so.name, t.local_attrs, t.local_methods, t.typeid
from sys.user$ u, sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
     sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where bitand(t.properties, 64) != 64 -- u.name
  and o.owner# = u.user#
  and o.oid$ = t.tvoid
  and o.subname IS NULL -- only the latest version
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.supertoid = so.oid$ (+) and so.owner# = su.user# (+)
  and t.package_obj# IS NULL  -- filter out PL/SQL types
UNION
select null, o.name, t.toid,
       decode(t.typecode, 108, 'OBJECT',
                          122, 'COLLECTION',
                          o.name),
       t.attributes, t.methods,
       decode(bitand(t.properties, 16), 16, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 256), 256, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 8), 8, 'NO', 'YES'),
       decode(bitand(t.properties, 65536), 65536, 'NO', 'YES'),
       su.name, so.name, t.local_attrs, t.local_methods, t.typeid
from sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
     sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where bitand(t.properties, 64) = 64  -- u.name is null
  and o.oid$ = t.tvoid
  and o.subname IS NULL -- only the latest version
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.supertoid = so.oid$ (+) and so.owner# = su.user# (+)
  and t.package_obj# IS NULL  -- filter out PL/SQL types

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, T.TOID
, DECODE(T.TYPECODE
, 108
, 'OBJECT'
, 122
, 'COLLECTION'
, O.NAME)
, T.ATTRIBUTES
, T.METHODS
, DECODE(BITAND(T.PROPERTIES
, 16)
, 16
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 256)
, 256
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 8)
, 8
, 'NO'
, 'YES')
, DECODE(BITAND(T.PROPERTIES
, 65536)
, 65536
, 'NO'
, 'YES')
, SU.NAME
, SO.NAME
, T.LOCAL_ATTRS
, T.LOCAL_METHODS
, T.TYPEID FROM SYS.USER$ U
, SYS.TYPE$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" SO
, SYS.USER$ SU WHERE BITAND(T.PROPERTIES
, 64) != 64 -- U.NAME
AND O.OWNER# = U.USER#
AND O.OID$ = T.TVOID
AND O.SUBNAME IS NULL -- ONLY THE LATEST VERSION
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND BITAND(T.PROPERTIES
, 2048) = 0 -- NOT SYSTEM-GENERATED
AND T.SUPERTOID = SO.OID$ (+)
AND SO.OWNER# = SU.USER# (+)
AND T.PACKAGE_OBJ# IS NULL -- FILTER OUT PL/SQL TYPES UNION SELECT NULL
, O.NAME
, T.TOID
, DECODE(T.TYPECODE
, 108
, 'OBJECT'
, 122
, 'COLLECTION'
, O.NAME)
, T.ATTRIBUTES
, T.METHODS
, DECODE(BITAND(T.PROPERTIES
, 16)
, 16
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 256)
, 256
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 8)
, 8
, 'NO'
, 'YES')
, DECODE(BITAND(T.PROPERTIES
, 65536)
, 65536
, 'NO'
, 'YES')
, SU.NAME
, SO.NAME
, T.LOCAL_ATTRS
, T.LOCAL_METHODS
, T.TYPEID FROM SYS.TYPE$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" SO
, SYS.USER$ SU WHERE BITAND(T.PROPERTIES
, 64) = 64 -- U.NAME IS NULL
AND O.OID$ = T.TVOID
AND O.SUBNAME IS NULL -- ONLY THE LATEST VERSION
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND BITAND(T.PROPERTIES
, 2048) = 0 -- NOT SYSTEM-GENERATED
AND T.SUPERTOID = SO.OID$ (+)
AND SO.OWNER# = SU.USER# (+)
AND T.PACKAGE_OBJ# IS NULL -- FILTER OUT PL/SQL TYPES