DBA_COLL_TYPES view tips

Oracle Tips by Burleson Consulting

DBA_COLL_TYPES

<< Return to the index

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

Related notes on DBA_COLL_TYPES:

Column description of the DBA_COLL_TYPES view:

OWNER

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

TYPE_NAME

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

COLL_TYPE

Description of DBA_COLL_TYPES.COLL_TYPE: "Collection type"

UPPER_BOUND

Description of DBA_COLL_TYPES.UPPER_BOUND: "Size of the FIXED ARRAY type or maximum size of the VARYING ARRAY type"

ELEM_TYPE_MOD

Description of DBA_COLL_TYPES.ELEM_TYPE_MOD: "Type modifier of the element"

ELEM_TYPE_OWNER

Description of DBA_COLL_TYPES.ELEM_TYPE_OWNER: "Owner of the type of the element"

ELEM_TYPE_NAME

Description of DBA_COLL_TYPES.ELEM_TYPE_NAME: "Name of the type of the element"

LENGTH

Description of DBA_COLL_TYPES.LENGTH: "Length of the CHAR element or maximum length of the VARCHAR or VARCHAR2 element"

PRECISION

Description of DBA_COLL_TYPES.PRECISION: "Decimal precision of the NUMBER or DECIMAL element or binary precision of the FLOAT element"

SCALE

Description of DBA_COLL_TYPES.SCALE: "Scale of the NUMBER or DECIMAL element"

CHARACTER_SET_NAME

Description of DBA_COLL_TYPES.CHARACTER_SET_NAME: "Character set name of the element"

ELEM_STORAGE

Description of DBA_COLL_TYPES.ELEM_STORAGE: "Storage optimization specification for VARRAY of numeric elements"

NULLS_STORED

Description of DBA_COLL_TYPES.NULLS_STORED: "Is null information stored with each VARRAY element?"

DBA_COLL_TYPES View Source

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

select u.name, o.name, co.name, c.upper_bound,
decode(bitand(c.properties, 32768), 32768, 'REF',
decode(bitand(c.properties, 16384), 16384, 'POINTER')),
nvl2(c.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=c.synobj#),
decode(bitand(et.properties, 64), 64, null, eu.name)),
nvl2(c.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=c.synobj#),
decode(et.typecode,
9, decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
96, decode(c.charsetform, 2, 'NCHAR', eo.name),
112, decode(c.charsetform, 2, 'NCLOB', eo.name),
eo.name)),
c.length, c.precision, c.scale,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(bitand(c.properties, 131072), 131072, 'FIXED',
decode(bitand(c.properties, 262144), 262144, 'VARYING')),
decode(bitand(c.properties, 65536), 65536, 'NO', 'YES')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.collection$ c, sys."_CURRENT_EDITION_OBJ" co,
sys."_CURRENT_EDITION_OBJ" eo, sys.user$ eu, sys.type$ et
where o.owner# = u.user#
and o.oid$ = c.toid
and o.subname IS NULL -- only the most recent version
and o.type# <> 10 -- must not be invalid
and c.coll_toid = co.oid$
and c.elem_toid = eo.oid$
and eo.owner# = eu.user#
and c.elem_toid = et.tvoid

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