Utility routines, Analyze, Time, Conversion etc.
Subprocedures:
COMPILE_SCHEMA Compile all procedures, functions, packages, and
triggers in the specified schema.
ANALYZE_SCHEMA Analyze all the tables, clusters, and indexes in
a schema.
ANALYZE_DATABASE Analyze all the tables, clusters, and indexes in
a database.
FORMAT_ERROR_STACK Function
Format the current error stack.
FORMAT_CALL_STACK Function
Format the current call stack.
IS_CLUSTER_DATABASE Function
Is this database running in cluster database mode.
GET_TIME Function
Current time in 100th's of a second.
GET_PARAMETER_VALUE Function
Get an init.ora parameter.
NAME_RESOLVE Resolve the given name.
NAME_TOKENIZE Parse the given name.
COMMA_TO_TABLE Convert a comma-separated list of names into a
PL/SQL table of names.
TABLE_TO_COMMA Converts a PL/SQL table of names into a comma-separated
list of names.
PORT_STRING Function
Return a string that uniquely identifies the version of Oracle
and the operating system.
DB_VERSION Version information for the database.
MAKE_DATA_BLOCK_ADDRESS Function
Create a data block address given a file number and a block number.
DATA_BLOCK_ADDRESS_FILE Function
Get the file number part of a data block address.
DATA_BLOCK_ADDRESS_BLOCK Function
Get the block number part of a data block address.
GET_HASH_VALUE Function
Compute a hash value for the given string.
ANALYZE_PART_OBJECT
Equivalent to ANALYZE TABLE|INDEX [schema.<object_name>]
EXEC_DDL_STATEMENT Execute the DDL statement in parse_string.
CURRENT_INSTANCE Function
Return the current connected instance number.
ACTIVE_INSTANCES Active instance numbers and names
Examples:
Compile all schema objects for the schema
SCOTT:
EXEC DBMS_UTILITY.compile_schema('SCOTT');
To collect stats for the schema
SCOTT:
EXEC DBMS_UTILITY.analyze_schema('SCOTT
Do not collect stats for either the SYS or SYSTEM schemas.
Stats can be removed with:
CONNECT SYS/password
EXEC DBMS_UTILITY.analyze_schema('SYS','DELETE')
To check if statistics have been deleted from a schema:
SELECT * FROM user_tables WHERE avg_space
is not null;
This will return 'no rows selected' when no stats are present.
Analyze_schema() is available in Oracle version 7.3.4 and above, however
if you have Oracle 8i or 9 you will obtain faster and better statistics from DBMS_STATS.GATHER_SCHEMA_STATS
For full documentation of the packaged procedures above see the Oracle Manual:
"Oracle9i Supplied PL/SQL Packages and Types Reference"
or the book Oracle Built in Packages
by Steven Feuerstein et al
“The fitter you are, the better you will survive. Start training now” ~ SAS
Survival Guide (John Wiseman)
Related Commands:
DBMS_DDL
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_DDL - compile/analyze objects
DBMS_IOT.BUILD_CHAIN_ROWS_TABLE
ANALYZE - Analyse Table | Index
EXPLAIN PLAN
STATISTICS - ASSOCIATE STATISTICS
STATISTICS - DISASSOCIATE STATISTICS
Related Views:
V$TIMER INDEX_STATS DBA_PART_COL_STATISTICS ALL_PART_COL_STATISTICS USER_PART_COL_STATISTICS DBA_SUBPART_COL_STATISTICS ALL_SUBPART_COL_STATISTICS USER_SUBPART_COL_STATISTICS DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS USER_TAB_COL_STATISTICS DBA_USTATS ALL_USTATS USER_USTATS