Find an object cause to invalid package in Oracle
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.