Find objects status in database

DBA can investigation which objects are invalid and they can take necessary action for those object.

Number of objects are invalid by object types

  SELECT object_type, COUNT ( * )
    FROM user_objects
   WHERE status = 'INVALID'
GROUP BY object_type;

Invalid objects with name of object.

SELECT object_name, owner, object_type
  FROM all_objects
 WHERE status LIKE 'INVALID';

Specific object details

SELECT *
  FROM dba_objects
 WHERE OBJECT_NAME LIKE '&object_name';

Compile invalid package

ALTER PACKAGE PACKAGENAME COMPILE BODY;

Define when procedure had compiled lastly.

SELECT OBJECT_NAME,LAST_DDL_TIME, TIMESTAMP
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = '&Procedure_name';