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