DBA_TYPE_ATTRS view tips

Oracle Tips by Burleson Consulting

DBA_TYPE_ATTRS

<< Return to the index

Oracle 11g makes the following comments about the DBA_TYPE_ATTRS table: "Description of attributes of all types in the database"

Related notes on DBA_TYPE_ATTRS:

Column description of the DBA_TYPE_ATTRS view:

OWNER

Description of DBA_TYPE_ATTRS.OWNER: "Owner of the type"

TYPE_NAME

Description of DBA_TYPE_ATTRS.TYPE_NAME: "Name of the type"

ATTR_NAME

Description of DBA_TYPE_ATTRS.ATTR_NAME: "Name of the attribute"

ATTR_TYPE_MOD

Description of DBA_TYPE_ATTRS.ATTR_TYPE_MOD: "Type modifier of the attribute"

ATTR_TYPE_OWNER

Description of DBA_TYPE_ATTRS.ATTR_TYPE_OWNER: "Owner of the type of the attribute"

ATTR_TYPE_NAME

Description of DBA_TYPE_ATTRS.ATTR_TYPE_NAME: "Name of the type of the attribute"

LENGTH

Description of DBA_TYPE_ATTRS.LENGTH: "Length of the CHAR attribute or maximum length of the VARCHAR or VARCHAR2 attribute"

PRECISION

Description of DBA_TYPE_ATTRS.PRECISION: "Decimal precision of the NUMBER or DECIMAL attribute or binary precision of the FLOAT attribute"

SCALE

Description of DBA_TYPE_ATTRS.SCALE: "Scale of the NUMBER or DECIMAL attribute"

CHARACTER_SET_NAME

Description of DBA_TYPE_ATTRS.CHARACTER_SET_NAME: "Character set name of the attribute"

ATTR_NO

Description of DBA_TYPE_ATTRS.ATTR_NO: "Syntactical order number or position of the attribute as specified in the type specification or CREATE TYPE statement (not to be used as ID number)"

INHERITED

Description of DBA_TYPE_ATTRS.INHERITED: "Is the attribute inherited from the supertype ?"

DBA_TYPE_ATTRS View Source

Oracle 11g's data dictionary defines the DBA_TYPE_ATTRS view using the following source query:

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 a.attr_toid = ao.oid$
and ao.owner# = au.user#
and a.attr_toid = at.tvoid
and a.attr_version# = at.version#
UNION
select null, 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."_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 is null
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 a.attr_toid = ao.oid$
and ao.owner# = au.user#
and a.attr_toid = at.tvoid
and a.attr_version# = at.version#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books