REM
/*
 * BadObjects.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

ACCEPT object_owner Prompt "Enter object owner to list bad objects for (wild cardsaccepted): "
PROMPT Listing all objects that have a status of INVALID..

COLUMN OBJECT_NAME FORMAT A32 Heading "Object Name"
COLUMN OBJECT_TYPE FORMAT A20 Heading "Object Type"
COLUMN OWNER       FORMAT A30 Heading "Object Owner"

Ttitle "Objects Owned by &&object_owner with Status of INVALID"

BREAK ON OWNER SKIP

SELECT	 owner
	,object_name
	,object_type
FROM	all_objects
WHERE	status='INVALID'
AND	owner LIKE UPPER('&&object_owner')
/
UNDEF object_owner
CLEAR COLUMNS

SET DOC off
/* SUMMARY - List out all invalid objects for a given owner (or all owners or owners matching a wild-card search). 
 *
 * DETAIL - this very simple single table SQL*Plus script produces a list of those objects in the database that are invalid for a given schema
 *          (or all schemas if % is used or schemas matching any wild card search string.
 *          
 *          Objects go invalid when things they depend on change. In most cases they automatically get re-compiled and become valid the next time they
 *          are accessed/used. Sometimes they will not - for instance when something they depend on has changed in a manner that means they are no longer valid
 *          (for instance if a table is dropped and the object - package, procedure, function, trigger etc - still uses the table). In either case, as a DBA
 *          or developer you will want to know what is invalid so that you can look into why it is invalid and perhaps to re-validate it rather than risking
 *          your application failing in the middle of the night. Locking issues are also a risk if you have a heavily used package that goes invalid and 
 *          multiple sessions all need to use it and try to validate it. The best strategy is to identify invalid objects as soon as possible and to fix them
 *          immediately.
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM