Title: An Enhancement to Recompiling Invalid Database Objects

Author: David Scerba, a database analyst, for state of New Jersey OIT, in Trenton, New
Jersey.

Recompile Invalid Database Objects II is an enhancement to Recompile Invalid Database Objects to correct the syntax of the
ALTER Package ... Package and Body that was described in Recompile Invalid Database Objects, January 28, 1999.

This script was used after upgrading Oracle and running catalog, catproc and catexp.

REM 3/20/1999 D. Scerba

REM Oracle Magazine January 28, 1999
REM Recompilation of Database Objects II
REM Corrected Syntax of Alter Package .... Procedure, Body.

REM Connect as SYSTEM

REM 1) Execute alter.sql.
REM 2) Review alter.lst.
REM 3) Execute alter.lst.

REM Execute Count Invalid Objects ... before and after running alter.lst

set heading on
set echo on

show user

Select * from V$DATABASE;

Select owner, object_type, count(*) from all_objects
where status='INVALID'
group by owner, object_type;

set heading off
set echo off
set feedback off

spool c:\oracle\alter.lst

REM Alters for Objects, no packages

select
  'alter ' || object_type || ' ' || owner || '.' || object_name ||
  ' compile;'
  from dba_objects
  where status = 'INVALID'
  and (object_type != 'PACKAGE' and object_type != 'PACKAGE BODY')
/

REM Alters for Package Body

select
  'alter PACKAGE ' || owner || '.' || object_name ||
  ' compile BODY;'
  from dba_objects
  where status = 'INVALID' and object_type = 'PACKAGE BODY'
/

REM Alters for Packages

select
  'alter ' || object_type || ' ' || owner || '.' || object_name ||
  ' compile PACKAGE;'
  from dba_objects
  where status = 'INVALID' and object_type = 'PACKAGE'
/

spool off

set feedback on
set echo on
set termout on
set heading on

@@alter.lst

Select owner, object_type, count(*) from all_objects
where status='INVALID'
group by owner, object_type;