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
ORACLE_MAINTAINED
PLS_INTEGER
256
STUBS
PLS_INTEGER
512
Dependencies
DBA_SCHEDULER_JOBS
DBMS_STATS
UTL_RECOMP_ALL_OBJECTS
DBA_SCHEDULER_RUNNING_JOBS
DBMS_UTILITY
UTL_RECOMP_CIRCULAR_MV
DBMS_EDITIONS_UTILITIES
DEPENDENCY$
UTL_RECOMP_COMPILED
DBMS_INTERNAL_LOGSTDBY
GV$PARAMETER
UTL_RECOMP_ERRORS
DBMS_LOCK
JOB_DEFINITION
UTL_RECOMP_INVALID_ALL
DBMS_OBJECTS_APPS_UTILS
JOB_DEFINITION_ARRAY
UTL_RECOMP_SKIP_LIST
DBMS_RANDOM
OBJ$
UTL_RECOMP_SORTED
DBMS_REGXDB
PLITBLM
V$PARAMETER
DBMS_SCHEDULER
UTL_IDENT
Documented
Yes
First Available
12.1
Pragmas
PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);
Security Model
Owned by SYS with no privileges granted.
Source
{ORACLE_HOME}/rdbms/admin/utlrcmp.sql
Subprograms
PARALLEL_SLAVE
Runs in each parallel thread to recompile invalid objects from utl_recomp_sorted
utl_recomp.parallel_slave(flags IN PLS_INTEGER);
exec utl_recomp.parallel_slave (2);
PL/SQL procedure successfully completed.
POPULATE_UTL_RECOMP_SKIP_LIST (new 23c)
Undocumented
Relocated from UTL_RECOMP2 in 21c
utl_recomp2.populate_utl_recomp_skip_list;
exec utl_recomp2.populate_utl_recomp_skip_list ;
PL/SQL procedure successfully completed.
PREP_RECOMP (new 23c)
Calll before recomop_parallel to preemptively invalidate objects that would be invalidated during recomp_parallel
utl_recomp.prep_recomp(schema IN VARCHAR2 := NULL);
exec utl_recomp.prep_recomp ;
PL/SQL procedure successfully completed.
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 IN VARCHAR2 := NULL,
flags IN PLS_INTEGER := 0);
exec utl_recomp.recomp_serial ('UWCLASS');
PL/SQL procedure successfully completed.
TRUNCATE_UTL_RECOMP_SKIP_LIST (new 23c)
Truncate the UTL_RECOMP2_SKIP_LIST table
Relocated from UTL_RECOMP2 in 21c
utl_recomp.truncate_utl_recomp_skip_list;
SELECT * FROM utl_recomp_skip_list;
no rows selected.
exec utl_recomp.truncate_utl_recomp_skip_list ;
SELECT * FROM utl_recomp_skip_list;
no rows selected.
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
Populates a row for every UTL_RECOMP job currently running
SELECT job_name
FROM dba_scheduler_running_jobs
WHERE job_name LIKE 'UTL_RECOMP_SLAVE_%';
no rows selected