LLI Wiki — My Digital Knowledge Base

Oracle: recompile invalid objects

Find, list and recompile invalid Oracle objects with utlrp.sql, ALTER COMPILE and DBMS_DDL.

This note shows how to find and recompile invalid objects in an Oracle database.

Find the errors

show errors;
select * from all_errors;

List invalid objects

Count them:

select count(*) from dba_objects where status = 'INVALID';

List them:

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Recompile everything

The utlrp.sql script recompiles all invalid objects. Run it right after an installation so users do not run into invalid objects. Oracle recommends running it at the end of any migration, upgrade or downgrade.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Equivalent calls, run as SYSDBA:

EXECUTE UTL_RECOMP.RECOMP_SERIAL();
@?/rdbms/admin/utlrp.sql

Recompile object by object

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

The DBMS_DDL package does the same:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

Recompile a whole schema

Run as SYSDBA:

EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(schema => 'MY_SCHEMA');
EXECUTE UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

Generate the compile statements

This SQL*Plus script writes one ALTER ... COMPILE statement per invalid package, function or procedure, then runs the result:

set heading off;
set feedback off;
set echo off;
set lines 999;

spool run_invalid.sql

select
    'ALTER ' || OBJECT_TYPE || ' ' ||
    OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
    dba_objects
where
    status = 'INVALID'
    and
    object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;

@run_invalid.sql
Mastodon LLIMastodon SocialFosstodon