How to Recompile all Invalid Database Objects


Cut and paste the sample code below into a file called compile.sql
Log into SQL*Plus as a user with DBA privileges (required to access the DBA_OBJECTS view)
Issue the following command from SQL*Plus to run the script:
SQL> @"compile.sql"
Sample Code
REM --------------------------------------------------------------------------
REM
REM compile.sql - Recompiles All Invalid Database Objects
REM 
REM This SQL script selects all INVALID database objects from DBA_OBJECTS and
REM attempts to recompile them. It then selects all remaining INVALID database 
REM objects to see how many did not compile successfully.
REM 
REM --------------------------------------------------------------------------
REM Set the environment variables
set echo off
set feedback off
set pages 0
set lines 100
prompt
prompt ************************************************
prompt Selecting all INVALID objects in the database...
prompt ************************************************
prompt
REM Start spooling the results into a temporary file
spool temp.sql
REM This select statement will generate a list of all invalid database objects
SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND
OBJECT_TYPE NOT IN ('PACKAGE BODY', 'TYPE BODY');
spool off;
prompt
prompt
prompt ****************************
prompt Compiling INVALID objects...
prompt ****************************
prompt
REM Run the spool file as a series of 'ALTER... COMPILE;' statements
@temp.sql
REM Remove the temporary spool file
host erase temp.sql
prompt
prompt ****************************************
prompt Compilation of INVALID objects complete.
prompt ****************************************
prompt
prompt
prompt **********************************************************
prompt Selecting all remaining INVALID objects in the database...
prompt **********************************************************
prompt
SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND
OBJECT_TYPE NOT IN ('PACKAGE BODY', 'TYPE BODY');
prompt
REM End of script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: