Oracle | UTL

Oracle UTL_RECOMP Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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 23ai)

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 23ai)

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 23ai)

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