53
DBMS_UTILITY

This package provides various utility subprograms.

DBMS_UTILITY submits a job for each partition. It is the users responsibility to control the number of concurrent jobs by setting the INIT.ORA parameter JOB_QUEUE_PROCESSES correctly.There is minimal error checking for correct syntax. Any error is reported in SNP trace files.

Requirements

DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE, COMPILE_SCHEMA, and ANALYZE_SCHEMA procedures. This is necessary so that the SQL works correctly.

This does not run as SYS. The privileges are checked via DBMS_DDL.

Types

type uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

Lists of "USER"."NAME"."COLUMN"@LINK should be stored here.

type name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

Lists of NAME should be stored here.

type dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;

Lists of database links should be stored here.

TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

The order in which objects should be generated is returned here.

TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

The order in which objects should be generated is returned here for users.

  TYPE instance_record IS RECORD (
       inst_number   NUMBER,
       inst_name     VARCHAR2(60));
  TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER;

The list of active instance number and instance name.

The starting index of instance_table is 1; instance_table is dense.

Summary of Subprograms

Table 53-1 DBMS_UTILITY Package Subprograms (Page 1 of 3)

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_PARALLEL_SERVER function	Finds out if this database is running in parallel server 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-separated list of names into a PL/SQL table of names.
TABLE_TO_COMMA procedure	Converts a PL/SQL table of names into a comma-separated 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 53-2 COMPILE_SCHEMA Procedure Parameters

Parameter	Description
schema	Name of the schema.

Exceptions

Table 53-3 COMPILE_SCHEMA Procedure Exceptions

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

ANALYZE_SCHEMA procedure

This procedure analyzes all the tables, clusters, and indexes in a schema.

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 53-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 non-zero.
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 53-5 ANALYZE_SCHEMA Procedure Exceptions

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

ANALYZE_DATABASE procedure

This procedure analyzes all the tables, clusters, and indexes in a database.

Syntax

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

Parameters

Table 53-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 non-zero.
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 53-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;

Parameters

None.

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;

Parameters

None.

Pragmas

pragma restrict_references(format_call_stack,WNDS);

Returns

This returns the call stack, up to 2000 bytes.

IS_PARALLEL_SERVER function

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

Syntax

DBMS_UTILITY.IS_PARALLEL_SERVER 
  RETURN BOOLEAN;

Parameters

None.

Returns

This returns TRUE if this instance was started in parallel server 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;

Parameters

None.

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 53-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 53-9 GET_PARAMETER_VALUE Function Returns

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

Return

Description

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 53-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 multi-byte).
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.

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-separated 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 53-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-separated list: Anything other than a comma-separated list is rejected. Commas inside double quotes do not count.

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-separated 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 53-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-separated list and the number of elements found in the table (n). Note that ',,,' || ',' || ',,,' = ',,,,,,,'.

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;

Parameters

None.

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 53-13 DB_VERSION Procedure Parameters

Parameter Description

Parameter	Description
version	A string which represents the internal software version of the database (e.g., 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 (e.g., 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 53-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 53-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 53-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 53-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 53-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 53-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 53-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 53-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 53-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;

Parameters

None.

ACTIVE_INSTANCES procedure

Syntax

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

Parameters

Table 53-23 ACTIVE_INSTANCES Procedure Parameters

Procedure	Description
instance_table	Contains a list of the active instance numbers and names. When no instance is up (or non-OPS setting), the list is empty.
instance_count	Number of active instances; 0 under non-OPS setting.

53DBMS_UTILITY

Requirements

Types

Summary of Subprograms

Table 53-1 DBMS_UTILITY Package Subprograms (Page 1 of 3)

COMPILE_SCHEMA procedure

Syntax

Parameters

Table 53-2 COMPILE_SCHEMA Procedure Parameters

Exceptions

Table 53-3 COMPILE_SCHEMA Procedure Exceptions

ANALYZE_SCHEMA procedure

Syntax

Parameters

Table 53-4 ANALYZE_SCHEMA Procedure Parameters

Exceptions

Table 53-5 ANALYZE_SCHEMA Procedure Exceptions

ANALYZE_DATABASE procedure

Syntax

Parameters

Table 53-6 ANALYZE_DATABASE Procedure Parameters

Exceptions

Table 53-7 ANALYZE_DATABASE Procedure Exceptions

FORMAT_ERROR_STACK function

Syntax

Parameters

Returns

FORMAT_CALL_STACK function

Syntax

Parameters

Pragmas

Returns

IS_PARALLEL_SERVER function

Syntax

Parameters

Returns

GET_TIME function

Syntax

Parameters

Returns

GET_PARAMETER_VALUE function

Syntax

Parameters

Table 53-8 GET_PARAMETER_VALUE Function Parameters

Returns

Table 53-9 GET_PARAMETER_VALUE Function Returns

Example

NAME_RESOLVE procedure

Syntax

Parameters

Table 53-10 NAME_RESOLVE Procedure Parameters

Exceptions

NAME_TOKENIZE procedure

Syntax

Parameters

COMMA_TO_TABLE procedure

Syntax

Parameters

Table 53-11 COMMA_TO_TABLE Procedure Parameters

Returns

Usage Notes

TABLE_TO_COMMA procedure

Syntax

Parameters

Table 53-12 TABLE_TO_COMMA Procedure Parameters

Returns

PORT_STRING function

Syntax

Parameters

Pragmas

DB_VERSION procedure

Syntax

Parameters

Table 53-13 DB_VERSION Procedure Parameters

MAKE_DATA_BLOCK_ADDRESS function

Syntax

Parameters

Table 53-14 MAKE_DATA_BLOCK_ADDRESS Function Parameters

Pragmas

Returns

53
DBMS_UTILITY