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';