Oracle | UTL

Oracle UTL_RECOMP Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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