BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_TYPES view tips

Oracle Tips by Burleson Consulting

DBA_TYPES

<< Return to the index

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

Related notes on DBA_TYPES:

Column description of the DBA_TYPES view:

OWNER

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

TYPE_NAME

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

TYPE_OID

Description of DBA_TYPES.TYPE_OID: "Object identifier (OID) of the type"

TYPECODE

Description of DBA_TYPES.TYPECODE: "Typecode of the type"

ATTRIBUTES

Description of DBA_TYPES.ATTRIBUTES: "Number of attributes in the type"

METHODS

Description of DBA_TYPES.METHODS: "Number of methods in the type"

PREDEFINED

Description of DBA_TYPES.PREDEFINED: "Is the type a predefined type?"

INCOMPLETE

Description of DBA_TYPES.INCOMPLETE: "Is the type an incomplete type?"

FINAL

Description of DBA_TYPES.FINAL: "Is the type a final type?"

INSTANTIABLE

Description of DBA_TYPES.INSTANTIABLE: "Is the type an instantiable type?"

SUPERTYPE_OWNER

Description of DBA_TYPES.SUPERTYPE_OWNER: "Owner of the supertype (null if type is not a subtype)"

SUPERTYPE_NAME

Description of DBA_TYPES.SUPERTYPE_NAME: "Name of the supertype (null if type is not a subtype)"

LOCAL_ATTRIBUTES

Description of DBA_TYPES.LOCAL_ATTRIBUTES: "Number of local (not inherited) attributes (if any) in the subtype"

LOCAL_METHODS

Description of DBA_TYPES.LOCAL_METHODS: "Number of local (not inherited) methods (if any) in the subtype"

TYPEID

Description of DBA_TYPES.TYPEID: "Type id value of the type"

DBA_TYPES View Source

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

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# (+)
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.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 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# (+)

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