Oracle Database - Compile

> Database > Oracle Database

1 - About

Invalid objects are recompiled automatically on use.

2 - How to compile

2.1 - ( 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
Advertising

2.2 - Admin script

The admin script:

 $ORACLE_HOME/rdbms/admin/utlrp.sql
 # in sqlplus
 @?/rdbms/admin/utlrp.sql

2.3 - 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');

2.4 - dbms_utility package

BEGIN
    DBMS_UTILITY.compile_schema(schema => 'MY_SCHEMA');
END;

3 - 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;

4 - Documentation / Reference

Advertising