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.

Parameters

Table 79-4 ANALYZE_SCHEMA Procedure Parameters

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.

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 dblinkOUT 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.

Table 79-12 TABLE_TO_COMMA Procedure Parameters

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 TWOTASKPROTOCOL 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

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.

Parameters

Table 79-20 GET_HASH_VALUE Function Parameters

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.