Finding Invalid Objects (procedure, package, trigger...)

Developers often change a small section of PL/SQL code that fails to compile upon execution, forcing an application failure. A simple query, reviewed daily, will help you spot these failures before the end user does:
col "Owner" format a12
col "Object" format a20
col "OType" format a12
col "Change DTE" format a20
select substr(owner,1,12) "Owner", substr(object_name,1,20) "Object", object_type "OType", to_char(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') "Change Date"
from dba_objects
where status <> 'VALID'
order by 1, 2;


The preceding example will display any objects that are INVALID, meaning they were never
compiled successfully or changes in dependent objects have caused them to become INVALID.
If we had a procedure PROCESS_DATE, for example, found to be INVALID, we could manually
recompile this procedure with the following command:
alter procedure PROCESS_DATE compile;
Once this command is executed and the PROCESS_DATE passes the recompile, the procedure
would be changed by Oracle automatically from INVALID to VALID. Another manual method
that exists is to call the DBMS_UTILITY.COMPILE_SCHEMA package procedure as outlined next
to recompile all stored procedures, functions, and packages for a given schema:
begin
dbms_utility.compile_schema('USERA');
end;
/



To find the state of all PL/SQL objects for your schema, execute the following:
column object_name format a20
column last_ddl_time heading 'last ddl time'
select object_type, object_name, status, created, last_ddl_time
from user_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');


Note:  the above was copied from "Oracle Database 10g Performance Tuning Tips and Techniques" book.