Monitoring Oracle8 Types, Collections, Methods, and Operators

Under Oracle8, Oracle added an entirely new set of objects to monitor, which have been called, variously, collections, types, user-defined types, ADTs, and other. I prefer the all-inclusive terms types, collections, and methods. Types allow the grouping of related data and, subsequently, the use of these grouped 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 to be an object and have its OIDs implicitly defined, it must be created as an object type from a defined type.

As explained in previous sections, there can be types that, while being named, have no other attributes. These are called incomplete types and are used where circular references may need to be defined. Of course, these incomplete types must be completed before an object is built from them.

 The major view for types is the DBA_TYPES. The cluster of views associated with types is shown in Figure 10.5.

Figure 10.5 DBA_TYPES view cluster.

Monitoring Types

A simple report to tell us some basic information about the types stored in our database is shown in Source 10.33. The report uses the DBA_TYPES view. To get detailed information on the attributes for each type, join to the DBA_TYPE_ATTRS view. The script in Source 10.33 produces a report similar to the one shown in Listing 10.31. The only columns not reported are TYPE_OID and TYPEID RAW.

SOURCE 10.33 Example of types report.

rem
rem NAME: types.sql
rem FUNCTION: Provide basic report of all database types
rem for a specific owner or all owners
rem HISTORY : MRA 6/15/97 Created
rem
COLUMN owner            FORMAT a10    HEADING 'Type|Owner'
COLUMN type_name        FORMAT a15    HEADING 'Type|Name'
COLUMN typecode         FORMAT a11    HEADING 'Type|Code'
COLUMN predefined       FORMAT a3     HEADING Pre?
COLUMN incomplete       FORMAT a3     HEADING Inc?
COLUMN methods          FORMAT 9999   HEADING '#|Methods'
COLUMN attributes       FORMAT 9999   HEADING '#|Attrib'
COLUMN final            FORMAT A5     HEADING 'Final'
COLUMN instantiable     FORMAT A5     HEADING 'Inst.'
COLUMN supertype_owner  FORMAT a10    HEADING 'SuperType|Owner'
COLUMN supertype_name   FORMAT a15    HEADING 'SuperType|Name'
COLUMN local_attributes FORMAT 99999  HEADING 'Local|Attri'
COLUMN local_methods FORMAT 99999     HEADING 'Local|Meth'
SET LINES 130 PAGES 58 VERIFY OFF FEEDBACK OFF
BREAK ON owner
START title132 'Database Types Report'
SPOOL rep_out\&db\types.lis
SELECT
     DECODE(owner, null,'SYS-GEN',owner) owner,
     type_name,
     typecode,
     attributes,
     methods,
     predefined,
     incomplete,
     final,
     Instantiable,
     Supertype_owner,
     Supertype_name,
     local_attributes,
     local_methods
FROM dba_types
WHERE owner LIKE '%&owner%'
ORDER BY owner, type_name;
SPOOL OFF
TTITLE OFF
SET VERIFY ON FEEDBACK ON LINES 80 PAGES 22
CLEAR COLUMNS
CLEAR BREAKS

LISTING 10.31 Example of output from basic types report.

Date:10/10/01
Page: 1
Time: 10:51 PM                             Database Types Report
SYSTEM

                                             galinux1 database

Notice that there are many kinds of types, many of which (those labeled SYSGEN) are system-generated at system build; others, such as OBJECT and COLLECTION, are user-defined. The collection types are further documented in the DBA_COLL_TYPES view.

Monitoring Type Collections  

Another kind of type is a collection (such as a VARRAY). The script in Source 10.34 generates a simple report that documents the important columns from DBA_COLL_TYPES. If you find you need the other data (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.

SOURCE 10.34 This script produces a report similar to that in Listing 10.32.

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
START title132 'Collection Type Report'
SPOOL rep_out\&db\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

LISTING 10.32  Example of output from the collection type report.

Date: 10/10/01
Page:   1
Time: 11:26 PM                    Collection Type Report
SYSTEM
                                     galinux1 database          

Element                                           Element
Collec.    Type                 Collec. VARRAY    Type
Type
Owner      Name                   Type   Limit    Owner   Name       Length  Prec.Scale  Nulls
---------- -------------------- ------- --------- ------- ---------  ------- ------ ------ -----
SYS        AQ$_SUBSCRIBERS      VARRAY       1024 SYS     AQ$_AGENT            YES           
SYS        DBMS_DEBUG_VC2COLL   TABLE                     VARCHAR2      1000   YES
SYS        ODCIRIDLIST          VARRAY      32767         VARCHAR2      5072   YES
SYS        ODCIGRANULELIST      VARRAY      65535         NUMBER               YES
WKSYS      VARCHAR2_TABLE_100   TABLE                     VARCHAR2       100   YES
WKSYS      VARCHAR2_TABLE_200   TABLE                     VARCHAR2       200   YES
KSYS       VARCHAR2_TABLE_300   TABLE                     VARCHAR2       300   YES           
WKSYS      VARCHAR2_TABLE_400   TABLE                     VARCHAR2       400   YES
SYSTEM     PICTURE_NT           TABLE             SYSTEM  PICTURE_T            YES

Monitoring Type Methods 

Types can also have methods associated with them. A type method is a procedure or function that is intrinsic to (an integral part of) the type and is defined (generally speaking) at the time the type is created. Take a close look at Listing 10.33, it only shows types with defined methods. The methods are documented in the DBA_TYPE_METHODS;  additional drill-down information is located in the DBA_METHOD_PARAMS and DBA_METHOD_RESULTS views. A simple report showing the types with methods in the database is given in Source 10.35. The report that results from running this source is shown in Listing 10.33.

SOURCE 10.35 Example of script to generate type methods report.

rem
rem NAME typ_meth.sql
rem FUNCTION : Create a report of type methods for a
rem specific user or all users
rem HISTORY: MRA 6/16/97 Created
rem          MRA 10/10/01 Updated to 9i
rem
COLUMN owner            FORMAT a10        HEADING 'Owner'
COLUMN type_name        FORMAT a25        HEADING 'Type|Name'
COLUMN method_name      FORMAT a25        HEADING 'Method|Name'
COLUMN method_type                        HEADING 'Method|Type'
COLUMN parameters       FORMAT 99999      HEADING '#|Param'
COLUMN results          FORMAT 99999      HEADING '#|Results'
COLUMN method_no        FORMAT 99999      HEADING 'Meth.|Number'
COLUMN final            FORMAT A5         HEADING 'Final'
COLUMN Instantiable     FORMAT A6         HEADING 'Instan'
COLUMN overriding       FORMAT A6         HEADING 'ORide?'
COLUMN Inherited        FORMAT A9         HEADING 'Inherited'
BREAK ON owner ON type_name
SET LINES 132 PAGES 58 VERIFY OFF FEEDBACK OFF
START title132 'Type Methods Report'
SPOOL rep_out\&db\typ_meth.lis
SELECT
     owner,
     type_name,
     method_name,
     method_no,
     method_type,
     parameters,
     results,
     final,
     Instantiable,
     Overriding,
     Inherited
FROM dba_type_methods
WHERE owner LIKE UPPER('%&owner%')
ORDER BY owner, type_name;
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET VERIFY ON FEEDBACK ON LINES 80 pages 22
TTITLE OFF

LISTING 10.33 Example of output from the type methods report.

The only object-oriented method of relating two object tables in Oracle8, Oracle8i, and Oracle9i is via a REF. A REF internalizes the foreign key relationship between a child and parent table. A REF always goes between child and parent since a REF can only reference one column. The DBA_REFS view documents existing REFs in the database. The script in Source 10.36 shows how a report can be generated to show the REFs in the database. Refer back to Figure 10.2, which shows the relationships between the views used to document REFs in the database, and Listing 10.34 shows an example of a report from the script in Source 10.36.

SOURCE 10.36 Example of REF column report.

rem
rem NAME: tab_ref.sql
rem FUNCTION: Generate a lit of all REF columns in the database
rem  for a specific user or all users
rem HISTORY: MRA 6/16/97 Created
rem
COLUMN owner             FORMAT a8  HEADING 'Owner'
COLUMN table_name        FORMAT a23 HEADING 'Table|Name'
COLUMN column_name       FORMAT a15 HEADING 'Column|Name'
COLUMN with_rowid        FORMAT a5  HEADING 'With|Rowid'
COLUMN is_scoped         FORMAT a6  HEADING 'Scoped'
COLUMN scope_table_owner FORMAT a8  HEADING 'Scope|Table|Owner'
COLUMN scope_table_name  FORMAT a15 HEADING 'Scope|Table|Name'
BREAK ON owner
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
START title132 'Database REF Report'
SPOOL rep_out\&db\tab_ref.lis
SELECT
     owner,
     table_name,
     column_name,
     with_rowid,
     is_scoped,
     scope_table_owner,
     scope_table_name
FROM
     dba_refs
WHERE
     Owner LIKE UPPER('%&owner%')
ORDER BY
     owner;
SPOOL OFF
SET FEEDBACK ON VERIFY ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF

LISTING 10.34 Example of output from the database REF report.

Date: 06/16/97                                               Page:   1
Time: 01:03 AM               Database REF Report                SYSTEM
                               ORTEST1 database

 
This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".