DBA_SQLJ_TYPE_ATTRS view tips

Oracle Tips by Burleson Consulting

DBA_SQLJ_TYPE_ATTRS

<< Return to the index

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

Related notes on DBA_SQLJ_TYPE_ATTRS:

Oracle DBA View List

Column description of the DBA_SQLJ_TYPE_ATTRS view:

OWNER

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

TYPE_NAME

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

ATTR_NAME

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

EXTERNAL_ATTR_NAME

Description of DBA_SQLJ_TYPE_ATTRS.EXTERNAL_ATTR_NAME: "External name of the attribute"

ATTR_TYPE_MOD

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

ATTR_TYPE_OWNER

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

ATTR_TYPE_NAME

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

LENGTH

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

PRECISION

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

SCALE

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

CHARACTER_SET_NAME

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

ATTR_NO

Description of DBA_SQLJ_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_SQLJ_TYPE_ATTRS.INHERITED: "Is the attribute inherited from the supertype ?"

DBA_SQLJ_TYPE_ATTRS View Source

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

select decode(bitand(t.properties, 64), 64, null, u.name),
o.name, a.name, a.externname,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
decode(bitand(at.properties, 64), 64, null, au.name),
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 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#
and t.externtype < 5

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