The UTL_RECOMP package recompiles invalid PL/SQL modules, invalid views, index types, and operators in a database.
This chapter contains the following topics:
Overview
Operational notes
Examples
UTL_RECOMP is particularly useful after a major-version upgrade that typically invalidates all PL/SQL objects. Although invalid objects are recompiled automatically on use, it is useful to run this before operation to eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.
This package must be run using ttIsql.
To use this package, you must be the instance administrator and run it as SYS.UTL_RECOMP.
This package expects the following packages to have been created with VALID status:
STANDARD (standard.sql)
DBMS_STANDARD (dbmsstdx.sql)
DBMS_RANDOM (dbmsrand.sql)
There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.
Because TimesTen does not support DBMS_SCHEDULER, the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore, there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL in TimesTen.
Recompile all objects sequentially:
Command> EXECUTE SYS.UTL_RECOMP.RECOMP_SERIAL();
Recompile objects in schema SCOTT sequentially:
Command> EXECUTE SYS.UTL_RECOMP.RECOMP_SERIAL('SCOTT');
Table 14-1 UTL_RECOMP Package Subprograms
| Subprogram | Description |
|---|---|
|
Recompiles invalid objects in a given schema, or all invalid objects in the database, in parallel. As noted earlier, in TimesTen the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore, there is no effective difference between |
|
|
Recompiles invalid objects in a given schema or all invalid objects in the database. |
Notes:
The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.
The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.
This procedure uses the information exposed in the DBA_Dependencies view to recompile invalid objects in the database, or in a given schema, in parallel.
In TimesTen, the threads value is always 1 regardless of how it is set. As a result, there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL.
UTL_RECOMP.RECOMP_PARALLEL( threads IN BINARY_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN BINARY_INTEGER DEFAULT 0);
Table 14-2 RECOMP_PARALLEL procedure parameters
| Parameter | Description |
|---|---|
|
|
The number of recompile threads to run in parallel In TimesTen, |
|
|
The schema in which to recompile invalid objects If |
|
|
Flag values intended for internal testing and diagnosability only |
This procedure recompiles invalid objects in a given schema or all invalid objects in the database.
UTL_RECOMP.RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN BINARY_INTEGER DEFAULT 0);