DBMS_UTILITY , 2 of 2

Summary of DBMS_UTILITY Subprograms

Table 79-1 DBMS_UTILITY Subprograms

Subprogram	Description
COMPILE_SCHEMA Procedure	Compiles all procedures, functions, packages, and triggers in the specified schema.
ANALYZE_SCHEMA Procedure	Analyzes all the tables, clusters, and indexes in a schema.
ANALYZE_DATABASE Procedure	Analyzes all the tables, clusters, and indexes in a database.
FORMAT_ERROR_STACK Function	Formats the current error stack.
FORMAT_CALL_STACK Function	Formats the current call stack.
IS_CLUSTER_DATABASE Function	Finds out if this database is running in cluster database mode.
GET_TIME Function	Finds out the current time in 100th's of a second.
GET_PARAMETER_VALUE Function	Gets the value of specified init.ora parameter.
NAME_RESOLVE Procedure	Resolves the given name.
NAME_TOKENIZE Procedure	Calls the parser to parse the given name.
COMMA_TO_TABLE Procedure	Converts a comma-delimited list of names into a PL/SQL table of names.
TABLE_TO_COMMA Procedure	Converts a PL/SQL table of names into a comma-delimited list of names.
PORT_STRING Function	Returns a string that uniquely identifies the version of Oracle and the operating system.
DB_VERSION Procedure	Returns version information for the database.
MAKE_DATA_BLOCK_ADDRESS Function	Creates a data block address given a file number and a block number.
DATA_BLOCK_ADDRESS_FILE Function	Gets the file number part of a data block address.
DATA_BLOCK_ADDRESS_BLOCK Function	Gets the block number part of a data block address.
GET_HASH_VALUE Function	Computes a hash value for the given string.
ANALYZE_PART_OBJECT Procedure
EXEC_DDL_STATEMENT Procedure	Executes the DDL statement in `parse_string`.
CURRENT_INSTANCE Function	Returns the current connected instance number.
ACTIVE_INSTANCES Procedure

COMPILE_SCHEMA Procedure

This procedure compiles all procedures, functions, packages, and triggers in the specified schema. After calling this procedure, you should select from view ALL_OBJECTS for items with status of INVALID to see if all objects were successfully compiled.

To see the errors associated with INVALID objects, you may use the Enterprise Manager command:

SHOW ERRORS <type> <schema>.<name>

Syntax

DBMS_UTILITY.COMPILE_SCHEMA (
   schema VARCHAR2);

Parameters

Table 79-2 COMPILE_SCHEMA Procedure Parameters

Parameter	Description
`schema`	Name of the schema.

Exceptions

Table 79-3 COMPILE_SCHEMA Procedure Exceptions

Exception	Description
`ORA-20000`	Insufficient privileges for some object in this schema.

ANALYZE_SCHEMA Procedure

This procedure runs the ANALYZE command on all the tables, clusters, and indexes in a schema. Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure.

Syntax

DBMS_UTILITY.ANALYZE_SCHEMA (
   schema           VARCHAR2, 
   method           VARCHAR2, 
   estimate_rows    NUMBER   DEFAULT NULL, 
   estimate_percent NUMBER   DEFAULT NULL, 
   method_opt       VARCHAR2 DEFAULT NULL);

Parameters

Table 79-4 ANALYZE_SCHEMA Procedure Parameters

Parameter	Description
`schema`	Name of the schema.
`method`	One of `ESTIMATE`, `COMPUTE` or `DELETE`. If `ESTIMATE`, then either `estimate_rows` or `estimate_percent` must be nonzero.
`estimate_rows`	Number of rows to estimate.
`estimate_percent`	Percentage of rows to estimate. If `estimate_rows` is specified, then ignore this parameter.
`method_opt`	Method options of the following format: `[ FOR TABLE ]` `[ FOR ALL [INDEXED] COLUMNS] [SIZE n]` `[ FOR ALL INDEXES ]`

Exceptions

Table 79-5 ANALYZE_SCHEMA Procedure Exceptions

Exception	Description
`ORA-20000`	Insufficient privileges for some object in this schema.

ANALYZE_DATABASE Procedure

This procedure runs the ANALYZE command on all the tables, clusters, and indexes in a database. Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_DATABASE_STATS procedure.

Syntax

DBMS_UTILITY.ANALYZE_DATABASE (
   method           VARCHAR2, 
   estimate_rows    NUMBER   DEFAULT NULL, 
   estimate_percent NUMBER   DEFAULT NULL, 
   method_opt       VARCHAR2 DEFAULT NULL);

Parameters

Table 79-6 ANALYZE_DATABASE Procedure Parameters

Parameter	Description
`method`	One of `ESTIMATE`, `COMPUTE` or `DELETE`. If `ESTIMATE`, then either `estimate_rows` or `estimate_percent` must be nonzero.
`estimate_rows`	Number of rows to estimate.
`estimate_percent`	Percentage of rows to estimate. If `estimate_rows` is specified, then ignore this parameter.
`method_opt`	Method options of the following format: `[ FOR TABLE ]` `[ FOR ALL [INDEXED] COLUMNS] [SIZE n]` `[ FOR ALL INDEXES ]`

Exceptions

Table 79-7 ANALYZE_DATABASE Procedure Exceptions

Exception	Description
`ORA-20000`	Insufficient privileges for some object in this database.

FORMAT_ERROR_STACK Function

This function formats the current error stack. This can be used in exception handlers to look at the full error stack.

Syntax

DBMS_UTILITY.FORMAT_ERROR_STACK 
  RETURN VARCHAR2;

Returns

This returns the error stack, up to 2000 bytes.

FORMAT_CALL_STACK Function

This function formats the current call stack. This can be used on any stored procedure or trigger to access the call stack. This can be useful for debugging.

Syntax

DBMS_UTILITY.FORMAT_CALL_STACK 
  RETURN VARCHAR2;

Pragmas

pragma restrict_references(format_call_stack,WNDS);

Returns

This returns the call stack, up to 2000 bytes.

IS_CLUSTER_DATABASE Function

This function finds out if this database is running in cluster database mode.

Syntax

DBMS_UTILITY.IS_CLUSTER_DATABASE 
  RETURN BOOLEAN;

Returns

This function returns TRUE if this instance was started in cluster database mode; FALSE otherwise.

GET_TIME Function

This function finds out the current time in 100th's of a second. It is primarily useful for determining elapsed time.

Syntax

DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;

Returns

Time is the number of 100th's of a second from some arbitrary epoch.

GET_PARAMETER_VALUE Function

This function gets the value of specified init.ora parameter.

Syntax

DBMS_UTILITY.GET_PARAMETER_VALUE (
   parnam IN     VARCHAR2,
   intval IN OUT BINARY_INTEGER,
   strval IN OUT VARCHAR2)
  RETURN BINARY_INTEGER;

Parameters

Table 79-8 GET_PARAMETER_VALUE Function Parameters

Parameter	Description
`parnam`	Parameter name.
`intval`	Value of an integer parameter or the value length of a string parameter.
`strval`	Value of a string parameter.

Returns

Table 79-9 GET_PARAMETER_VALUE Function Returns

Return Description

Return	Description
`partyp`	Parameter type: 0 if parameter is an integer/boolean parameter 1 if parameter is a string/file parameter

partyp

Parameter type:

0 if parameter is an integer/boolean parameter

1 if parameter is a string/file parameter

Example

DECLARE
  parnam VARCHAR2(256);
  intval BINARY_INTEGER;
  strval VARCHAR2(256);
  partyp BINARY_INTEGER;
BEGIN
  partyp := dbms_utility.get_parameter_value('max_dump_file_size',
                                              intval, strval);
  dbms_output.put('parameter value is: ');
  IF partyp = 1 THEN
    dbms_output.put_line(strval);
  ELSE
    dbms_output.put_line(intval);
  END IF;
  IF partyp = 1 THEN
    dbms_output.put('parameter value length is: ');
    dbms_output.put_line(intval);
  END IF;
  dbms_output.put('parameter type is: ');
  IF partyp = 1 THEN
    dbms_output.put_line('string');
  ELSE
    dbms_output.put_line('integer');
  END IF;
END;

NAME_RESOLVE Procedure

This procedure resolves the given name, including synonym translation and authorization checking as necessary.

Syntax

DBMS_UTILITY.NAME_RESOLVE (
   name          IN  VARCHAR2, 
   context       IN  NUMBER,
   schema        OUT VARCHAR2, 
   part1         OUT VARCHAR2, 
   part2         OUT VARCHAR2,
   dblink        OUT VARCHAR2, 
   part1_type    OUT NUMBER, 
   object_number OUT NUMBER);

Parameters

Table 79-10 NAME_RESOLVE Procedure Parameters

Parameter	Description
`name`	Name of the object. This can be of the form [[a.]b.]c[@d], where a, b, c are SQL identifier and d is a dblink. No syntax checking is performed on the dblink. If a dblink is specified, or if the name resolves to something with a dblink, then object is not resolved, but the `schema`, `part1`, `part2` and `dblink` `OUT` parameters are filled in. a, b and c may be delimited identifiers, and may contain NLS characters (single and multibyte).
`context`	Must be an integer between 0 and 8.
`schema`	Schema of the object: c. If no schema is specified in `name`, then the `schema` is determined by resolving the name.
`part1`	First part of the name. The type of this name is specified `part1_type` (synonym, procedure or package).
`part2`	If this is non-`NULL`, then this is a procedure name within the package indicated by `part1`.
`dblink`	If this is non-`NULL`, then a database link was either specified as part of `name` or `name` was a synonym which resolved to something with a database link. In this later case, `part1_type` indicates a synonym.
`part1_type`	Type of `part1` is: 5 - synonym 7 - procedure (top level) 8 - function (top level) 9 - package If a synonym, then it means that `name` is a synonym that translates to something with a database link. In this case, if further name translation is desired, then you must call the `DBMS_UTILITY`.`NAME_RESOLVE` procedure on this remote node.
`object_number`	Object identifier

Exceptions

All errors are handled by raising exceptions. A wide variety of exceptions are possible, based on the various syntax error that are possible when specifying object names.

NAME_TOKENIZE Procedure

This procedure calls the parser to parse the given name as "a [. b [. c ]][@ dblink ]". It strips double quotes, or converts to uppercase if there are no quotes. It ignores comments of all sorts, and does no semantic analysis. Missing values are left as NULL.

Syntax

DBMS_UTILITY.NAME_TOKENIZE ( 
   name    IN  VARCHAR2,
   a       OUT VARCHAR2,
   b       OUT VARCHAR2,
   c       OUT VARCHAR2,
   dblink  OUT VARCHAR2, 
   nextpos OUT BINARY_INTEGER);

Parameters

For each of a, b, c, dblink, tell where the following token starts in anext, bnext, cnext, dnext respectively.

COMMA_TO_TABLE Procedure

This procedure converts a comma-delimited list of names into a PL/SQL table of names. This uses NAME_TOKENIZE to figure out what are names and what are commas.

Syntax

DBMS_UTILITY.COMMA_TO_TABLE ( 
   list   IN  VARCHAR2,
   tablen OUT BINARY_INTEGER,
   tab    OUT UNCL_ARRAY);

Parameters

Table 79-11 COMMA_TO_TABLE Procedure Parameters

Parameter	Description
`list`	Comma separated list of tables.
`tablen`	Number of tables in the PL/SQL table.
`tab`	PL/SQL table which contains list of table names.

Returns

A PL/SQL table is returned, with values 1..n and n+1 is null.

Usage Notes

The list must be a non-empty comma-delimited list: Anything other than a comma-delimited list is rejected. Commas inside double quotes do not count.

Entries in the comma-delimited list cannot include multibyte characters such as hyphens (-).

The values in tab are cut from the original list, with no transformations.

TABLE_TO_COMMA Procedure

This procedure converts a PL/SQL table of names into a comma-delimited list of names. This takes a PL/SQL table, 1..n, terminated with n+1 null.

Syntax

DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  UNCL_ARRAY, 
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2);

Parameters

Table 79-12 TABLE_TO_COMMA Procedure Parameters

Parameter	Description
`tab`	PL/SQL table which contains list of table names.
`tablen`	Number of tables in the PL/SQL table.
`list`	Comma separated list of tables.

Returns

Returns a comma-delimited list and the number of elements found in the table.

PORT_STRING Function

This function returns a string that identifies the operating system and the TWO TASK PROTOCOL version of the database. For example, "VAX/VMX-7.1.0.0"

The maximum length is port-specific.

Syntax

DBMS_UTILITY.PORT_STRING 
   RETURN VARCHAR2;

Pragmas

pragma restrict_references(port_string, WNDS, RNDS, WNPS, RNPS);

DB_VERSION Procedure

This procedure returns version information for the database.

Syntax

DBMS_UTILITY.DB_VERSION (
   version       OUT VARCHAR2,
   compatibility OUT VARCHAR2);

Parameters

Table 79-13 DB_VERSION Procedure Parameters

Parameter Description

Parameter	Description
version	A string which represents the internal software version of the database (for example, 7.1.0.0.0). The length of this string is variable and is determined by the database version.
compatibility	The compatibility setting of the database determined by the "compatible" `init`.`ora` parameter. If the parameter is not specified in the `init.ora` file, then `NULL` is returned.

version

A string which represents the internal software version of the database (for example, 7.1.0.0.0).

The length of this string is variable and is determined by the database version.

compatibility

The compatibility setting of the database determined by the "compatible" init.ora parameter.

If the parameter is not specified in the init.ora file, then NULL is returned.

MAKE_DATA_BLOCK_ADDRESS Function

This function creates a data block address given a file number and a block number. A data block address is the internal structure used to identify a block in the database. This function is useful when accessing certain fixed tables that contain data block addresses.

Syntax

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (
   file  NUMBER, 
   block NUMBER) 
  RETURN NUMBER;

Parameters

Table 79-14 MAKE_DATA_BLOCK_ADDRESS Function Parameters

Parameter	Description
file	File that contains the block.
block	Offset of the block within the file in terms of block increments.

Pragmas

pragma restrict_references(make_data_block_address, WNDS, RNDS, WNPS, RNPS);

Returns

Table 79-15 MAKE_DATA_BLOCK_ADDRESS Function Returns

Returns	Description
dba	Data block address.

DATA_BLOCK_ADDRESS_FILE Function

This function gets the file number part of a data block address.

Syntax

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (
   dba NUMBER) 
  RETURN NUMBER;

Parameters

Table 79-16 DATA_BLOCK_ADDRESS_FILE Function Parameters

Parameter	Description
dba	Data block address.

Pragmas

pragma restrict_references(data_block_address_file, WNDS, RNDS, WNPS, RNPS);

Returns

Table 79-17 DATA_BLOCK_ADDRESS_FILE Function Returns

Returns	Description
file	File that contains the block.

DATA_BLOCK_ADDRESS_BLOCK Function

This function gets the block number part of a data block address.

Syntax

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (
   dba NUMBER) 
  RETURN NUMBER;

Parameters

Table 79-18 DATA_BLOCK_ADDRESS_BLOCK Function Parameters

Parameter	Description
dba	Data block address.

Pragmas

pragma restrict_references(data_block_address_block, WNDS, RNDS, WNPS, RNPS);

Returns

Table 79-19 DATA_BLOCK_ADDRESS_BLOCK Function Returns

Returns	Description
block	Block offset of the block.

GET_HASH_VALUE Function

This function computes a hash value for the given string.

Syntax

DBMS_UTILITY.GET_HASH_VALUE (
   name      VARCHAR2, 
   base      NUMBER, 
   hash_size NUMBER)
  RETURN NUMBER;

Parameters

Table 79-20 GET_HASH_VALUE Function Parameters

Parameter	Description
name	String to be hashed.
base	Base value for the returned hash value to start at.
hash_size	Desired size of the hash table.

Pragmas

pragma restrict_references(get_hash_value, WNDS, RNDS, WNPS, RNPS);

Returns

A hash value based on the input string. For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value. Using a power of 2 for the hash_size parameter works best.

ANALYZE_PART_OBJECT Procedure

This procedure is equivalent to SQL:

"ANALYZE TABLE|INDEX [<schema>.]<object_name> PARTITION <pname> [<command_type>] 
[<command_opt>] [<sample_clause>]

For each partition of the object, run in parallel using job queues.

Syntax

DBMS_UTILITY.ANALYZE_PART_OBJECT (
   schema        IN VARCHAR2 DEFAULT NULL,
   object_name   IN VARCHAR2 DEFAULT NULL,
   object_type   IN CHAR     DEFAULT 'T',
   command_type  IN CHAR     DEFAULT 'E',
   command_opt   IN VARCHAR2 DEFAULT NULL,
   sample_clause IN VARCHAR2 DEFAULT 'SAMPLE 5 PERCENT');

Parameters

Table 79-21 ANALYZE_PART_OBJECT Procedure Parameters

Parameter	Description
schema	Schema of the `object_name`.
object_name	Name of object to be analyzed, must be partitioned.
object_type	Type of object, must be `T` (table) or `I` (index).
command_type	Must be one of the following: C (compute statistics) E (estimate statistics) D (delete statistics) V (validate structure)
command_opt	Other options for the command type. For `C`, `E` it can be `FOR` table, `FOR` all `LOCAL` indexes, `FOR` all columns or combination of some of the 'for' options of analyze statistics (table). For `V`, it can be `CASCADE` when `object_type` is T.
sample_clause	The sample clause to use when `command_type` is '`E`'.

EXEC_DDL_STATEMENT Procedure

This procedure executes the DDL statement in parse_string.

Syntax

DBMS_UTILITY.EXEC_DDL_STATEMENT (
   parse_string IN VARCHAR2);

Parameters

Table 79-22 EXEC_DDL_STATEMENT Procedure Parameters

Parameter	Description
parse_string	DDL statement to be executed.

CURRENT_INSTANCE Function

This function returns the current connected instance number. It returns NULL when connected instance is down.

Syntax

DBMS_UTILITY.CURRENT_INSTANCE
   RETURN NUMBER;

ACTIVE_INSTANCES Procedure

Syntax

DBMS_UTILITY.ACTIVE_INSTANCE (
   instance_table   OUT INSTANCE_TABLE,
   instance_count   OUT NUMBER);

Parameters

Table 79-23 ACTIVE_INSTANCES Procedure Parameters

Procedure	Description
instance_table	Contains a list of the active instance numbers and names. When no instance is up, the list is empty.
instance_count	Number of active instances.