UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below:

* schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
* threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
* flags - Used for internal diagnostics and testing only.

The following examples show how these procedures care used:

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

There are a number of restrictions associated with the use of this package including:

* Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
* The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
* The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
* Runnig DDL operations at the same time as this package may result in deadlocks.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-1001613/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-1001613/