DBA_COLL_TYPES

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================




DBA_COLL_TYPES describes all named collection types (arrays, nested tables, object tables, and so on) in the database. Its columns are the same as those in "ALL_COLL_TYPES".

Related Views

· ALL_COLL_TYPES describes all named collection types (varrays and nested tables) accessible to the current user.

· USER_COLL_TYPES describes all named collection types owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the collection
TYPE_NAME	VARCHAR2(30)	NOT NULL	Name of the collection
COLL_TYPE	VARCHAR2(30)	NOT NULL	Description of the collection, such as VARYING ARRAY, [nested] TABLE
UPPER_BOUND	NUMBER		For varrays only, maximum size
ELEM_TYPE_MOD	VARCHAR2(7)		Type modifier of the element
ELEM_TYPE_OWNER	VARCHAR2(30)		Owner of the type upon which the collection is based. This value is useful primarily in the case of a user-defined type.
ELEM_TYPE_NAME	VARCHAR2(30)		Name of the datatype or user-defined type upon which the collection is based
LENGTH	NUMBER		Length of CHAR elements or maximum length of VARCHAR or VARCHAR2 elements
PRECISION	NUMBER		Decimal precision of NUMBER or DECIMAL elements; binary precision of FLOAT elements
SCALE	NUMBER		Scale of NUMBER or DECIMAL elements
CHARACTER_SET_NAME	VARCHAR2(44)		Name of the character set (CHAR_CS | NCHAR_CS)
ELEM_STORAGE	VARCHAR2(7)		Obsolete column
NULLS_STORED	VARCHAR2(3)		Obsolete column

Note:

1. Query collection types used in Oracle Spatial:

SELECT TYPE_NAME, COLL_TYPE, UPPER_BOUND, ELEM_TYPE_OWNER,

ELEM_TYPE_NAME, LENGTH, CHARACTER_SET_NAME, NULLS_STORED

FROM DBA_COLL_TYPES where owner = 'MDSYS';

TYPE_NAME	COLL_TYPE	UPPER_ BOUND	ELEM_ TYPE_ OWNER	ELEM_TYPE_NAME	LENGTH	CHARACTER_ SET_ NAME	NULLS_ STORED
F81_INDEX_OBJ_ARRAY	VARYING ARRAY	1000000	MDSYS	F81_INDEX_OBJECT			YES
F81_NT_IND_TYPE	TABLE		MDSYS	F81_INDEX_OBJECT			YES
H81_INDEX_OBJ_ARRAY	VARYING ARRAY	1000000	MDSYS	H81_INDEX_OBJECT			YES
H81_NT_IND_TYPE	TABLE		MDSYS	H81_INDEX_OBJECT			YES
NUM_ARRAY	VARYING ARRAY	100		NUMBER			YES
RDF_ALIASES	TABLE		MDSYS	RDF_ALIAS			YES
RDF_LONGVARCHARARRAY	VARYING ARRAY	32767		VARCHAR2	4000	CHAR_CS	YES
RDF_MODELS	TABLE			VARCHAR2	25	CHAR_CS	YES
RDF_RULEBASES	TABLE			VARCHAR2	25	CHAR_CS	YES
RDF_VARCHARARRAY	VARYING ARRAY	32767		VARCHAR2	30	CHAR_CS	YES
SDORIDLIST	VARYING ARRAY	1048576		VARCHAR2	24	CHAR_CS	YES
SDO_ADDR_ARRAY	VARYING ARRAY	1000	MDSYS	SDO_GEO_ADDR			YES
SDO_DIM_ARRAY	VARYING ARRAY	4	MDSYS	SDO_DIM_ELEMENT			YES
SDO_EDGE_ARRAY	VARYING ARRAY	1000000	MDSYS	SDO_NUMBER_ARRAY			YES
SDO_ELEM_INFO_ARRAY	VARYING ARRAY	1048576		NUMBER			YES
SDO_KEYWORDARRAY	VARYING ARRAY	10000		VARCHAR2	9000	CHAR_CS	YES
SDO_LIST_TYPE	VARYING ARRAY	2147483647		NUMBER			YES
SDO_NUMBER_ARRAY	VARYING ARRAY	1048576		NUMBER			YES
SDO_NUMTAB	TABLE			NUMBER			YES
SDO_ORDINATE_ARRAY	VARYING ARRAY	1048576		NUMBER			YES
SDO_RASTERSET	TABLE		MDSYS	SDO_RASTER			YES
SDO_REGAGGRSET	TABLE		MDSYS	SDO_REGAGGR			YES
SDO_REGIONSET	TABLE		MDSYS	SDO_REGION			YES
SDO_ROWIDSET	TABLE		MDSYS	SDO_ROWIDPAIR			YES
SDO_SRID_CHAIN	VARYING ARRAY	1048576		NUMBER			YES
SDO_SRID_LIST	VARYING ARRAY	1048576		NUMBER			YES
SDO_STATTAB	TABLE		MDSYS	SDO_STAT			YES
SDO_STRING_ARRAY	VARYING ARRAY	1048576		VARCHAR2	32	CHAR_CS	YES
SDO_TFM_CHAIN	VARYING ARRAY	1048576		NUMBER			YES
SDO_TGL_OBJECT_ARRAY	VARYING ARRAY	1000000	MDSYS	SDO_TGL_OBJECT			YES
SDO_TOPO_GEOMETRY_LAYER_ARRAY	VARYING ARRAY	1000	MDSYS	SDO_TOPO_GEOMETRY_LAYER			YES
SDO_TOPO_NSTD_TBL	TABLE			NUMBER			YES
SDO_TOPO_OBJECT_ARRAY	VARYING ARRAY	1000000	MDSYS	SDO_TOPO_OBJECT			YES
SDO_TRANSIENT_RULE_SET	VARYING ARRAY	1048576	MDSYS	SDO_TRANSIENT_RULE			YES
SDO_VPOINT_TYPE	VARYING ARRAY	64		NUMBER			YES
STRING_ARRAY	VARYING ARRAY	1048576		VARCHAR2	256	CHAR_CS	YES
ST_CURVE_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_CURVE			YES
ST_DOUBLE_PRECISION_ARRAY	VARYING ARRAY	1048576		DOUBLE PRECISION			YES
ST_GEOMETRY_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_GEOMETRY			YES
ST_LINESTRING_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_LINESTRING			YES
ST_POINT_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_POINT			YES
ST_POLYGON_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_POLYGON			YES
ST_SURFACE_ARRAY	VARYING ARRAY	1048576	MDSYS	ST_GEOMETRY			YES
V81_INDEX_OBJ_ARRAY	VARYING ARRAY	1000000	MDSYS	V81_INDEX_OBJECT			YES
V81_NT_IND_TYPE	TABLE		MDSYS	V81_INDEX_OBJECT			YES
VERTEX_SET_TYPE	TABLE		MDSYS	VERTEX_TYPE			YES

Oracle data dictionary views

Oracle dynamic performance views