| |
 |
|
Oracle collections
Oracle Database Tips by Donald Burleson |
The all-inclusive terms, such as types, Oracle
collections, and methods are preferred. Types allow the
grouping of related data sets to form more complex objects. For
example, a table object might consist of standard columns, a
user-defined type, a nested table (built from a type), and a
collection (called a VARRAY), which is in itself a type. In order to
declare a table as an object and have its OIDs implicitly defined,
it must be created as an object type from a defined type.
Monitoring Oracle Collections
Another kind of type is a collection (such as a
VARRAY). The script shown below generates a simple report that
documents the important columns from dba_coll_types. If other
data is needed (for simple scalar collections), then by all means
add them to the script. To determine the attributes that map into
each collection, join to the dba_type_attrs view.
rem
rem NAME: coll_type.sql
rem FUNCTION: Document the
collection types in the database
rem for a specified user or
all users
rem HISTORY: MRA 6/15/97
Created
rem MRA 10/10/01
Updated to 9i
rem
COL owner FORMAT
a10 HEADING 'Collec.|Owner'
COL type_name FORMAT
a16 HEADING 'Type|Name'
COL coll_type FORMAT
a15 HEADING 'Collec.|Type'
COL upper_bound
HEADING 'VARRAY|Limit'
COL elem_type_owner FORMAT
a10 HEADING 'Elementary|Type|Owner'
COL elem_type_name FORMAT
a11 HEADING 'Elementary|Type|Name'
SET PAGES 58 LINES 130
VERIFY OFF FEEDBACK OFF
ttitle 'Collection Type
Report'
SPOOL col_type.lis
select
owner,
type_name,
coll_type,
upper_bound,
elem_type_mod,
elem_type_owner,
elem_type_name,
length,
precision,
scale,
elem_storage,
nulls_stored
FROM dba_coll_types
WHERE owner LIKE '%&owner%'
/
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
TTILTE OFF
SET VERIFY ON FEEDBACK ON
|
and include the URL for the page.
