Oracle | UTL

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	RECOMP_SERIAL	UTL_RECOMP Related Queries
RECOMP_PARALLEL

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