VIEW: SYS.DBA_TYPE_ATTRS

Source

View Text - Preformatted

select u.name, o.name, a.name,
       decode(bitand(a.properties, 32768), 32768, 'REF',
              decode(bitand(a.properties, 16384), 16384, 'POINTER')),
       nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
            where o.owner#=u.user# and o.obj#=a.synobj#),
            decode(bitand(at.properties, 64), 64, null, au.name)),
       nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=a.synobj#),
            decode(at.typecode,
                   9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
                   96, decode(a.charsetform, 2, 'NCHAR', ao.name),
                   112, decode(a.charsetform, 2, 'NCLOB', ao.name),
                   ao.name)),
       a.length, a.precision#, a.scale,
       decode(a.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(a.charsetid),
                             4, 'ARG:'||a.charsetid),
       a.attribute#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
     sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where bitand(t.properties, 64) != 64 -- u.name
  and o.owner# = u.user#
  and o.oid$ = t.toid
  and o.subname IS NULL -- get the latest version only
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.toid = a.toid
  and t.version# = a.version#
  and t.package_obj# IS NULL -- filter out plsql types
  and a.attr_toid = ao.oid$
  and ao.owner# = au.user#
  and a.attr_toid = at.tvoid
  and a.attr_version# = at.version#

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, A.NAME
, DECODE(BITAND(A.PROPERTIES
, 32768)
, 32768
, 'REF'
, DECODE(BITAND(A.PROPERTIES
, 16384)
, 16384
, 'POINTER'))
, NVL2(A.SYNOBJ#
, (SELECT U.NAME
FROM USER$ U
, "_CURRENT_EDITION_OBJ" O
WHERE O.OWNER#=U.USER#
AND O.OBJ#=A.SYNOBJ#)
, DECODE(BITAND(AT.PROPERTIES
, 64)
, 64
, NULL
, AU.NAME))
, NVL2(A.SYNOBJ#
, (SELECT O.NAME
FROM "_CURRENT_EDITION_OBJ" O
WHERE O.OBJ#=A.SYNOBJ#)
, DECODE(AT.TYPECODE
, 9
, DECODE(A.CHARSETFORM
, 2
, 'NVARCHAR2'
, AO.NAME)
, 96
, DECODE(A.CHARSETFORM
, 2
, 'NCHAR'
, AO.NAME)
, 112
, DECODE(A.CHARSETFORM
, 2
, 'NCLOB'
, AO.NAME)
, AO.NAME))
, A.LENGTH
, A.PRECISION#
, A.SCALE
, DECODE(A.CHARSETFORM
, 1
, 'CHAR_CS'
, 2
, 'NCHAR_CS'
, 3
, NLS_CHARSET_NAME(A.CHARSETID)
, 4
, 'ARG:'||A.CHARSETID)
, A.ATTRIBUTE#
, DECODE(BITAND(NVL(A.XFLAGS
, 0)
, 1)
, 1
, 'YES'
, 'NO') FROM SYS.USER$ U
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.TYPE$ T
, SYS.ATTRIBUTE$ A
, SYS."_CURRENT_EDITION_OBJ" AO
, SYS.USER$ AU
, SYS.TYPE$ AT WHERE BITAND(T.PROPERTIES
, 64) != 64 -- U.NAME
AND O.OWNER# = U.USER#
AND O.OID$ = T.TOID
AND O.SUBNAME IS NULL -- GET THE LATEST VERSION ONLY
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND BITAND(T.PROPERTIES
, 2048) = 0 -- NOT SYSTEM-GENERATED
AND T.TOID = A.TOID
AND T.VERSION# = A.VERSION#
AND T.PACKAGE_OBJ# IS NULL -- FILTER OUT PLSQL TYPES
AND A.ATTR_TOID = AO.OID$
AND AO.OWNER# = AU.USER#
AND A.ATTR_TOID = AT.TVOID
AND A.ATTR_VERSION# = AT.VERSION#