Oracle UTL_RECOMP
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This script provides a packaged interface to recompile invalid PL/SQL modules, Java classes, indextypes and operators in a database sequentially or in parallel.

This package can handle the recompile of any database or container with the sole exception of PDB$SEED which is, by definition, READ ONLY. If PDB$SEED contains invalid objects your database is toast or, at the least, PDB$SEED, is unusable.
AUTHID DEFINER
Constants
Name Data Type Value
COMPILE_LOG (Obsolete) PLS_INTEGER 2
NO_REUSE_SETTINGS (Obsolete) PLS_INTEGER 4
RANDOM_ORDER PLS_INTEGER 8
REVERSE_ORDER PLS_INTEGER 16
SPECS_ONLY PLS_INTEGER 32
TYPES_ONLY PLS_INTEGER 64
NEW_EDITION PLS_INTEGER 128
Dependencies
DBA_SCHEDULER_JOBS DBMS_SCHEDULER UTL_IDENT
DBA_SCHEDULER_RUNNING_JOBS DBMS_STATS UTL_RECOMP_ALL_OBJECTS
DBMS_EDITIONS_UTILITIES DBMS_UTILITY UTL_RECOMP_COMPILED
DBMS_INTERNAL_LOGSTDBY DEPENDENCY$ UTL_RECOMP_ERRORS
DBMS_LOCK GV$PARAMETER UTL_RECOMP_INVALID_ALL
DBMS_OBJECTS_APPS_UTILS JOB_DEFINITION UTL_RECOMP_SKIP_LIST
DBMS_RANDOM JOB_DEFINITION_ARRAY UTL_RECOMP_SORTED
DBMS_REGXDB PLITBLM  
Documented Yes
First Available 12.1.0
Security Model Owned by SYS with no privileges granted. As of version 11.2.0.2 supports TimesTen.
Source {ORACLE_HOME}/rdbms/admin/utlrcmp.sql
Subprograms
 
PARALLEL_SLAVE
An internal function that runs in each parallel thread to recompile invalid objects from utl_recomp_sorted utl_recomp.parallel_slave(flags IN PLS_INTEGER);
Internal to package
 
RECOMP_PARALLEL
Recompiles all objects using 4 parallel threads utl_recomp.recomp_parallel(
threads PLS_INTEGER := NULL,
schema  VARCHAR2    := NULL,
flags   PLS_INTEGER := 0);
exec utl_recomp.recomp_parallel(4);

PL/SQL procedure successfully completed.
Recompile schema objects using the default degree of parallelism exec utl_recomp.recomp_parallel(NULL, 'UWCLASS');

PL/SQL procedure successfully completed.
Recompiles all objects using 2 parallel threads and allows other applications to use the job queue concurrently exec utl_recomp.recomp_parallel(2, NULL, utl_recomp.random_order);

PL/SQL procedure successfully completed.
 
RECOMP_SERIAL
Recompile all objects sequentially utl_recomp.recomp_serial;
exec utl_recomp.recomp_serial;

PL/SQL procedure successfully completed.
Recompile all objects in a schema sequentially utl_recomp.recomp_serial(schema VARCHAR2 := NULL, flags PLS_INTEGER := 0);
exec utl_recomp.recomp_serial('UWCLASS');

PL/SQL procedure successfully completed.
 
UTL_RECOMP Related Queries
Are there invalid database objects SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;

OWNER       OBJECT_TYPE    COUNT(*)
----------- ------------- ---------
C##UWCLASS  PACKAGE BODY          1
SYS         PACKAGE BODY          4
SYS         PACKAGE               1
Is a UTL_RECOMP job currently running SELECT job_name
FROM dba_scheduler_running_jobs
WHERE job_name LIKE 'UTL_RECOMP_SLAVE_%';

no rows selected

Related Topics
Built-in Functions
Built-in Packages
DBMS_UTILITY.COMPILE_SCHEMA
UTL_RECOMP2
Packages
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx