invalids.sql

---------------------------------------------------------------------------
-- Filename:    invalids.sql
-- Purpose:     Lists all invalid objects and provides SQL to (attempt to)
--              repair them.
-- Author:      Chas. Dye (cdye@excitecorp.com)
-- Date:        28-Jun-1996
---------------------------------------------------------------------------
column object_name  format a25      heading "Object Name"
column status       format a7       heading "Status"
column owner        format a12      heading "Owner"
column object_type  format a12      heading "Object Type"
column created      format a20      heading "Date Created"
column fix          format a70      heading "Run these statements to repair"

SELECT  object_name, status, object_type, owner, created
FROM    dba_objects
WHERE   status != 'VALID'
/

SELECT
      'ALTER ' || 
      DECODE( object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' ||
      lower(owner)||'.'|| lower(object_name) ||
      DECODE( object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;') fix
FROM  dba_objects
WHERE object_type IN (        'FUNCTION',
                              'PACKAGE',
                              'PACKAGE BODY',
                              'PROCEDURE',
                              'TYPE',
                              'TRIGGER',
                              'VIEW'
                     )
AND   status = 'INVALID'
/

Get Oracle Distributed Systems now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial