Find an object cause to invalid package in Oracle When DB admin drops then create a table, some packages became invalid. I can easily recompile them. But how to know exactly what is the table(dependant object) that is the cause that  a package to became invalid?
After the tables were dropped, views related to dependencies no longer contain information about the dropped table, but you can query DBA/ALL/USER_ERRORS.

SQL&gt; create table t1 (c1 number);

Table created.

SQL&gt; create or replace procedure p1 as
  2    l_c number;
  3  begin
  4    select count(*) into l_c from t1;
  5  end;
  6  /

Procedure created.

SQL&gt; select status from user_objects where object_name = 'P1';

STATUS
-------
VALID

SQL&gt; drop table t1 purge;

Table dropped.

SQL&gt; select status from user_objects where object_name = 'P1';

STATUS
-------
INVALID


At this point, the procedure becomes INVALID automatically, but you can not query errors related to it yet:

SQL&gt; select * from user_errors where name = 'P1';

no rows selected


So compile it, and query again:

SQL&gt; alter procedure p1 compile;

Warning: Procedure altered with compilation errors.

SQL&gt; select * from user_errors where name = 'P1';

NAME TYPE        SEQUENCE       LINE   POSITION TEXT                                            ATTRIBUTE MESSAGE_NUMBER
---- --------- ---------- ---------- ---------- ----------------------------------------------- --------- --------------
P1   PROCEDURE          1          4         33 PL/SQL: ORA-00942: table or view does not exist ERROR                  0
P1   PROCEDURE          2          4          3 PL/SQL: SQL Statement ignored                   ERROR                  0


Now you could view the source of P1 at line 4, position 33, and check what if refers to.