DBMS_UTILITY
The DBMS_UTILITY package provides various utility subprograms.
This chapter contains the following topics:
Using DBMS_UTILITY
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
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:
Summary of DBMS_UTILITY Subprograms
Table 104-1 DBMS_UTILITY Package SubprogramsSubprogram | Description |
---|---|
Analyzes all the tables, clusters, and indexes in a database [see also Deprecated Subprograms] | |
Returns the active instance | |
Analyzes the given tables and indexes | |
Analyzes all the tables, clusters, and indexes in a schema [see also Deprecated Subprograms] | |
Canonicalizes a given string | |
Converts a comma-delimited list of names into a PL/SQL table of names | |
Compiles all procedures, functions, packages, and triggers in the specified schema | |
Creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement | |
Returns the current connected instance number | |
Gets the block number part of a data block address | |
Gets the file number part of a data block address | |
Returns version information for the database | |
Executes the DDL statement in parse_string | |
Formats the backtrace from the point of the current error to the exception handler where the error has been caught | |
Formats the current error stack | |
Formats the current call stack | |
Returns the current CPU time in 100th's of a second | |
Shows the dependencies on the object passed in. | |
Computes a hash value for the given string | |
Gets the value of specified init.ora parameter | |
Finds out the current time in 100th's of a second | |
Finds out if this database is running in cluster database mode | |
Creates a data block address given a file number and a block number | |
Resolves the given name | |
Calls the parser to parse the given name | |
Returns a string that uniquely identifies the version of Oracle and the operating system | |
Converts a PL/SQL table of names into a comma-delimited list of names | |
Converts a PL/SQL table of names into a comma-delimited list of names |
ANALYZE_DATABASE Procedure
Note: This subprogam is obsolete with release Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see "DBMS_STATS". |
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 ParametersExceptions
Table 104-3 ANALYZE_DATABASE Procedure ExceptionsException | 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 ParametersProcedure | 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 ParametersUsage Notes
For each partition of the object, run in parallel using job queues.
ANALYZE_SCHEMA Procedure
Note: This subprogam is obsolete with Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see "DBMS_STATS". |
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 ParametersExceptions
Table 104-7 ANALYZE_SCHEMA Procedure ExceptionsException | 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 ParametersParameter | 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
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 ParametersParameter | 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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31349667/viewspace-2124550/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31349667/viewspace-2124550/