April 1999
Intermediate to advanced
552 pages
15h 39m
English
---------------------------------------------------------------------------
-- 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'
/