Search This Blog

Saturday, July 30, 2011

Recompiling invalid objects

When applying patches or making changes to a database I often invalidate many objects with one change. As long as the object requires no changes, the following SQL*Plus script will recompile any object that is invalid:

set feedback off
      set verify off
      set echo off
      set pagesize 0
      set heading off
      spool compile.sql
      select  'alter ' ||
             decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
             ' ' ||
             object_name||
             ' compile' ||
             decode(object_type, 'PACKAGE BODY', ' body;', ';')
      from   dba_objects
      where  status = 'INVALID'
      /
      spool off
      set feedback on
      set verify on
      set heading on
      set pagesize 40
      @compile
   

No comments:

Post a Comment