Oracle identify invalid views, procedures, functions or packages

Have you ever wondered how to identify/find any invalid view's, procedure's, functions or packages...

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

In an ideal scenario you will get an empty result which shows everything is fine. If not you need to kick the rebuild off:

Depending on the invalid object type, you use the commands below to fix it (expecting it is not invalid by intention)

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Oracle identify invalid views, procedures, functions or packages

Labels