Oracle Database - Compile

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;

Documentation / Reference


Powered by ComboStrap