Oracle | UTL

Oracle UTL_RECOMP Version 23c

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

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