Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc).
To find all the INVALID objects in the schema, issue
SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';
One problem with the way oracle maintains this metadata is, changing the underlying table on which the DB Code depends, oracle marks the objects are INVALID even though the underlying table may have changed in such a way, that it does not affect the DB Code at all (like adding a new column, or making a colum nullable). Here is some code which shows you what I mean. Run it through SQLPlus.
COLUMN OBJECT_NAME FORMAT A30 COLUMN STATUS FORMAT A15 spool objects.log CREATE TABLE FOO (ID NUMBER(10), NAME VARCHAR2(30)); CREATE OR REPLACE TRIGGER TRIG_FOO BEFORE INSERT OR UPDATE ON FOO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.name IS NULL THEN :NEW.name := 'NOT AVAILABLE'; END IF; END; / CREATE OR REPLACE FUNCTION FUNCTION_GET_NAME_FOR_FOOID(inFooId number) RETURN VARCHAR2 IS fooName VARCHAR2(30); BEGIN BEGIN SELECT name INTO fooName FROM foo WHERE id = inFooId ; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'NOT FOUND'; END; RETURN fooName; END; / SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID'; ALTER TABLE FOO ADD ( DESCRIPTION VARCHAR2(100)); SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID'; spool off
To get the objects back to VALID status, all that needs to be done is
ALTER TRIGGER TRIG_FOO COMPILE; ALTER FUNCTION FUNCTION_GET_NAME_FOR_FOOID COMPILE;
SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';