About
Invalid objects are recompiled automatically on use.
How to compile
( SQL | PL/SQL )
ALTER FUNCTION my_function COMPILE;
ALTER MATERIALIZED VIEW my_mater_view COMPILE;
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER SYNONYM my_synonym COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER TYPE my_type COMPILE;
ALTER TYPE my_type COMPILE BODY;
ALTER VIEW my_view COMPILE;
Not all objects can be compiled. Database link cannot be compiled
SET heading off;
SET feedback off;
SET echo off;
SET lines 9999;
Spool compile_invalid.SQL
select
case
when object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW') then 'alter ' || OBJECT_TYPE || ' ' || owner || '.' || object_name || ' compile;'
when object_type in ('PACKAGE BODY') THEN 'alter package ' || owner || '.' || object_name || ' compile body;'
end case as ddl_statement
from
all_objects
where
status='INVALID'
order by object_name;
spool off;
@compile_invalid.SQL
Admin script
The admin script:
$ORACLE_HOME/rdbms/admin/utlrp.sql
# in sqlplus
@?/rdbms/admin/utlrp.sql
UTL_RECOMP package
The UTL_RECOMP package recompiles invalid PL/SQL modules, invalid views, Java classes, indextypes and operators in a database, either sequentially or in parallel.
Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
dbms_utility package
begin
dbms_utility.compile_schema(schema => 'MY_SCHEMA');
end;
Metadata
SELECT
object_type,
object_name,
status
FROM user_objects -- or use the dba_objects and the owner column
WHERE status = 'INVALID'
ORDER BY object_type, object_name;