The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the
1) RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the
2) 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');
check out job_queue_processes by :
select NAME,VALUE from v$parameter2 where NAME='job_queue_processes';
or show parameter job_queue_processes (by sys user)
There are a number of restrictions associated with the use of this package including:
* Parallel execution is performed 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.