DBMS_UTILITY

The DBMS_UTILITY package provides various utility subprograms.

This chapter contains the following topics:

• Using DBMS_UTILITY
  • Security Model
  • Types
  • Deprecated Subprograms
• Summary of DBMS_UTILITY Subprograms

---

Using DBMS_UTILITY

• Security Model
• Types
• Deprecated Subprograms

---

Security Model

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

The package does not run as SYS. The privileges are checked using DBMS_DDL.

---

Types

• dblink_array
• index_table_type
• instance_record
• lname_array
• name_array
• number_array
• uncl_array

dblink_array

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

Lists of database links should be stored here.

index_table_type

TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;  

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

instance_record

 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.

lname_array

TYPE lname_array IS TABLE OF VARCHAR2(4000) index by BINARY_INTEGER;  

Lists of Long NAME should be stored here, it includes fully qualified attribute names.

name_array

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

Lists of NAME should be stored here.

number_array

TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  

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

uncl_array

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

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

---

Deprecated Subprograms

Obsolete with Oracle Database Release 10g:

• ANALYZE_DATABASE Procedure
• ANALYZE_SCHEMA Procedure

---

Summary of DBMS_UTILITY Subprograms

Table 104-1  DBMS_UTILITY Package Subprograms

Subprogram	Description
ANALYZE_DATABASE Procedure	Analyzes all the tables, clusters, and indexes in a database [see also Deprecated Subprograms]
ACTIVE_INSTANCES Procedure	Returns the active instance
ANALYZE_PART_OBJECT Procedure	Analyzes the given tables and indexes
ANALYZE_SCHEMA Procedure	Analyzes all the tables, clusters, and indexes in a schema [see also Deprecated Subprograms]
CANONICALIZE Procedure	Canonicalizes a given string
COMMA_TO_TABLE Procedures	Converts a comma-delimited list of names into a PL/SQL table of names
COMPILE_SCHEMA Procedure	Compiles all procedures, functions, packages, and triggers in the specified schema
CREATE_ALTER_TYPE_ERROR_TABLE Procedure	Creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement
CURRENT_INSTANCE Function	Returns the current connected instance number
DATA_BLOCK_ADDRESS_BLOCK Function	Gets the block number part of a data block address
DATA_BLOCK_ADDRESS_FILE Function	Gets the file number part of a data block address
DB_VERSION Procedure	Returns version information for the database
EXEC_DDL_STATEMENT Procedure	Executes the DDL statement in parse_string
FORMAT_ERROR_BACKTRACE Function	Formats the backtrace from the point of the current error to the exception handler where the error has been caught
FORMAT_ERROR_STACK Function	Formats the current error stack
FORMAT_CALL_STACK Function	Formats the current call stack
GET_CPU_TIME Function	Returns the current CPU time in 100th's of a second
GET_DEPENDENCY Procedure	Shows the dependencies on the object passed in.
GET_HASH_VALUE Function	Computes a hash value for the given string
GET_PARAMETER_VALUE Function	Gets the value of specified init.ora parameter
GET_TIME Function	Finds out the current time in 100th's of a second
IS_CLUSTER_DATABASE Function	Finds out if this database is running in cluster database mode
MAKE_DATA_BLOCK_ADDRESS Function	Creates a data block address given a file number and a block number
NAME_RESOLVE Procedure	Resolves the given name
NAME_TOKENIZE Procedure	Calls the parser to parse the given name
PORT_STRING Function	Returns a string that uniquely identifies the version of Oracle and the operating system
TABLE_TO_COMMA Procedures	Converts a PL/SQL table of names into a comma-delimited list of names
VALIDATE Procedure	Converts a PL/SQL table of names into a comma-delimited list of names

---

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 104-2 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 104-3 ANALYZE_DATABASE Procedure Exceptions

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

Usage Notes

Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_TABLE_STATS or DBMS_STATS.GATHER_INDEX_STATS procedure.

---

ACTIVE_INSTANCES Procedure

This procedure returns the active instance.

Syntax

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

Parameters

Table 104-4 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.

---

ANALYZE_PART_OBJECT Procedure

This procedure is equivalent to SQL:

"ANALYZE TABLE|INDEX [.] PARTITION  [] 
[] [] 

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 104-5 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 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'.

Usage Notes

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

---

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 104-6 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 104-7 ANALYZE_SCHEMA Procedure Exceptions

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

---

CANONICALIZE Procedure

This procedure canonicalizes the given string. The procedure handles a single reserved or key word (such as 'table'), and strips off white spaces for a single identifier so that ' table ' becomes TABLE.

Syntax

DBMS_UTILITY.CANONICALIZE(  name           IN    VARCHAR2,  canon_name     OUT   VARCHAR2,  canon_len      IN    BINARY_INTEGER);

Parameters

Table 104-8  CANONICALIZE Procedure Parameters

Parameter	Description
name	The string to be canonicalized.
canon_name	The canonicalized string
canon_len	The length of the string (in bytes) to canonicalize.

Return Values

Returns the first canon_len bytes in canon_name

Usage Notes

• If name is NULL, canon_name becomes NULL.
• If name is not a dotted name, and if name begins and ends with a double quote, remove both quotes. Alternatively, convert to upper case with NLS_UPPER. Note that this case does not include a name with special characters, such as a space, but is not doubly quoted.
• If name is a dotted name (such as a."b".c), for each component in the dotted name in the case in which the component begins and ends with a double quote, no transformation will be performed on this component. Alternatively, convert to upper case with NLS_UPPER and apply begin and end double quotes to the capitalized form of this component. In such a case, each canonicalized component will be concatenated together in the input position, separated by ".".
• Any other character after a[.b]* will be ignored.
• The procedure does not handle cases like 'A B.'

Examples

• a becomes A
• "a" becomes a
• "a".b becomes "a"."B"
• "a".b,c.f becomes "a"."B" with",c.f" ignored.

---

COMMA_TO_TABLE Procedures

These procedures converts a comma-delimited list of names into a PL/SQL table of names. The second version supports fully-qualified attribute names.

Syntax

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

Parameters

Table 104-9 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.

Return Values

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.

---