7.15 DBMS_UTILITY

Table of Contents Previous Next


7 Built-In Packages : 7.15 DBMS_UTILITY

The DBMS_UTILITY package provides various utility programs.
ANALYZE_DATABASE(method [, estimate_rows [, estimate_percent [, method_opt ]]])
ANALYZE_PART_OBJECT(schema, object_name [, object_type [, command_type [, command_opt [, sample_clause ]]]])
ANALYZE_SCHEMA(schema, method [, estimate_rows [, estimate_percent [, method_opt ]]])
CANONICALIZE(name, canon_name OUT, canon_len)
COMMA_TO_TABLE(list, tablen OUT, tab OUT)
DB_VERSION(version OUT, compatibility OUT)
EXEC_DDL_STATEMENT(parse_string)
GET_DEPENDENCY(type, schema, name)
GET_HASH_VALUE(name, base, hash_size)
GET_PARAMETER_VALUE(parnam, intval OUT, strval OUT)
NAME_TOKENIZE(name, a OUT, b OUT, c OUT, dblink OUT, nextpos OUT)
TABLE_TO_COMMA(tab, tablen OUT, list OUT)
Advanced Server's implementation of DBMS_UTILITY is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
Used by the INVALIDATE procedure.
The LNAME_ARRAY is for storing lists of long names including fully-qualified names.
7.15.2 UNCL_ARRAY
The UNCL_ARRAY is for storing lists of users and names.
The ANALYZE_DATABASE(), ANALYZE_SCHEMA() and ANALYZE_PART_OBJECT() procedures provide the capability to gather statistics on tables in the database. When you execute the ANALYZE statement, Postgres samples the data in a table and records distribution statistics in the pg_statistics system table.
ANALYZE_DATABASE, ANALYZE_SCHEMA, and ANALYZE_PART_OBJECT differ primarily in the number of tables that are processed:
ANALYZE_DATABASE analyzes all tables in all schemas within the current database.
ANALYZE_SCHEMA analyzes all tables in a given schema (within the current database).
ANALYZE_PART_OBJECT analyzes a single table.
The syntax for the ANALYZE commands are:
ANALYZE_DATABASE(method VARCHAR2 [, estimate_rows NUMBER
[, estimate_percent NUMBER [, method_opt VARCHAR2 ]]])
ANALYZE_SCHEMA(schema VARCHAR2, method VARCHAR2
[, estimate_rows NUMBER [, estimate_percent NUMBER
[, method_opt VARCHAR2 ]]])
ANALYZE_PART_OBJECT(schema VARCHAR2, object_name VARCHAR2
[, object_type CHAR [, command_type CHAR
[, command_opt VARCHAR2 [, sample_clause ]]]])
Parameters - ANALYZE_DATABASE and ANALYZE_SCHEMA
method determines whether the ANALYZE procedure populates the pg_statistics table or removes entries from the pg_statistics table. If you specify a method of DELETE, the ANALYZE procedure removes the relevant rows from pg_statistics. If you specify a method of COMPUTE or ESTIMATE, the ANALYZE procedure analyzes a table (or multiple tables) and records the distribution information in pg_statistics. There is no difference between COMPUTE and ESTIMATE; both methods execute the Postgres ANALYZE statement. All other parameters are validated and then ignored.
Number of rows upon which to base estimated statistics. One of estimate_rows or estimate_percent must be specified if method is ESTIMATE.
Percentage of rows upon which to base estimated statistics. One of estimate_rows or estimate_percent must be specified if method is ESTIMATE.
Parameters - ANALYZE_PART_OBJECT
Type of analyze functionality to perform. Valid values are: E - gather estimated statistics based upon on a specified number of rows or a percentage of rows in the sample_clause clause; C - compute exact statistics; or V – validate the structure and integrity of the partitions.
For command_type C or E, can be any combination of:
For command_type V, can be CASCADE if object_type is T.
If command_type is E, contains the following clause to specify the number of rows or percentage or rows on which to base the estimate.
SAMPLE n { ROWS | PERCENT }
The CANONICALIZE procedure performs the following operations on an input string:
CANONICALIZE(name VARCHAR2, canon_name OUT VARCHAR2,
canon_len BINARY_INTEGER)
Number of bytes in name to canonicalize starting from the first character.
The following procedure applies the CANONICALIZE procedure on its input parameter and displays the results.
The COMMA_TO_TABLE procedure converts a comma-delimited list of names into a table of names. Each entry in the list becomes a table entry. The names must be formatted as valid identifiers.
COMMA_TO_TABLE(list VARCHAR2, tablen OUT BINARY_INTEGER,
tab OUT { LNAME_ARRAY | UNCL_ARRAY })
A DBMS_UTILITY LNAME_ARRAY (as described in Section 7.9.1).
A DBMS_UTILITY UNCL_ARRAY (as described in Section 7.9.2).
The following procedure uses the COMMA_TO_TABLE procedure to convert a list of names to a table. The table entries are then displayed.
7.15.6 DB_VERSION
The DB_VERSION procedure returns the version number of the database.
DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)
The EXEC_DDL_STATEMENT provides the capability to execute a DDL command.
EXEC_DDL_STATEMENT(parse_string VARCHAR2)
The DDL command to be executed.
If the parse_string does not include a valid DDL statement, Advanced Server returns the following error:
The FORMAT_CALL_STACK function returns the formatted contents of the current call stack.
The GET_CPU_TIME function returns the CPU time in hundredths of a second from some arbitrary point in time.
cputime NUMBER GET_CPU_TIME
The following SELECT command retrieves the current CPU time, which is 603 hundredths of a second or .0603 seconds.
The GET_DEPENDENCY procedure provides the capability to list the objects that are dependent upon the specified object. GET_DEPENDENCY does not show dependencies for functions or procedures.
GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2,
name VARCHAR2)
The object type of name. Valid values are INDEX, PACKAGE, PACKAGE BODY, SEQUENCE, TABLE, TRIGGER, TYPE and VIEW.
The GET_HASH_VALUE function provides the capability to compute a hash value for a given string.
hash NUMBER GET_HASH_VALUE(name VARCHAR2, base NUMBER,
hash_size NUMBER)
The following anonymous block creates a table of hash values using the ename column of the emp table and then displays the key along with the hash value. The hash values start at 100 with a maximum of 1024 distinct values.
The GET_PARAMETER_VALUE procedure provides the capability to retrieve database initialization parameter settings.
status BINARY_INTEGER GET_PARAMETER_VALUE(parnam VARCHAR2,
intval OUT INTEGER, strval OUT VARCHAR2)
Returns 0 if the parameter value is INTEGER or BOOLEAN. Returns 1 if the parameter value is a string.
7.15.13 GET_TIME
The GET_TIME function provides the capability to return the current time in hundredths of a second.
time NUMBER GET_TIME
The NAME_TOKENIZE procedure parses a name into its component parts. Names without double quotes are uppercased. The double quotes are stripped from names with double quotes.
NAME_TOKENIZE(name VARCHAR2, a OUT VARCHAR2,
b OUT VARCHAR2,c OUT VARCHAR2, dblink OUT VARCHAR2,
nextpos OUT BINARY_INTEGER)
a[.b[.c]][@dblink ]
Tokenize the name, edb.list_emp:
Tokenize the name, "edb"."Emp_Admin".update_emp_sal:
Tokenize the name edb.emp@edb_dblink:
The TABLE_TO_COMMA procedure converts table of names into a comma-delimited list of names. Each table entry becomes a list entry. The names must be formatted as valid identifiers.
TABLE_TO_COMMA(tab { LNAME_ARRAY | UNCL_ARRAY },
tablen OUT BINARY_INTEGER, list OUT VARCHAR2)
A DBMS_UTILITY LNAME_ARRAY (as described in Section 7.9.1).
A DBMS_UTILITY UNCL_ARRAY (as described in Section 7.9.2).
The following example first uses the COMMA_TO_TABLE procedure to convert a comma-delimited list to a table. The TABLE_TO_COMMA procedure then converts the table back to a comma-delimited list that is displayed.

7 Built-In Packages : 7.15 DBMS_UTILITY

Table of Contents Previous Next