SQL> create public synonym xxx for mcdonac.t;

Synonym created.

SQL> select * from all_objects where status = 'INVALID' and object_name = 'XXX';

no rows selected

SQL> drop table t;

Table dropped.

SQL> select * from all_objects where status = 'INVALID' and object_name = 'XXX';

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED   LAST_DDL_ TIMESTAMP           STATUS  T
---------- -------------- ----------------------- --------- --------- ------------------- ------- -
G S  NAMESPACE
- - ----------
EDITION_NAME
----------------------------------------------------------------------------------------------------
SHARING            E O A
------------------ - - -
DEFAULT_COLLATION
----------------------------------------------------------------------------------------------------
D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
- - ------------- ------------- -------------- --------------
PUBLIC                         XXX

     96571                SYNONYM                 21-AUG-18 21-AUG-18 2018-08-21:19:16:40 INVALID N
N N          1

NONE               N N N

N N


1 row selected.

SQL> set serverout on
SQL> declare
  2    cursor l_c is (select * from all_objects where status = 'INVALID' and object_name = 'XXX');
  3    l_sql varchar2(3500);
  4    l_type varchar2(3500);
  5  begin
  6    for i in l_c loop
  7
  8        if i.object_name = 'SYNONYM' and i.owner = 'PUBLIC'
  9        then
 10          l_sql := 'alter PUBLIC SYNONYM ' || i.object_name || ' compile';
 11        else
 12          select decode(i.object_type,'PACKAGE BODY','PACKAGE',i.object_type)
 13            into l_type
 14            from dual;
 15
 16          l_sql := 'alter ' || l_type || ' ' || i.owner || '.' || i.object_name || ' compile';
 17        end if;
 18
 19        dbms_output.put_line(l_sql);
 20        execute immediate l_sql;
 21
 22    end loop;
 23  end;
 24  /
alter SYNONYM PUBLIC.XXX compile
declare
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
ORA-06512: at line 20
ORA-06512: at line 20


Elapsed: 00:00:00.05
SQL>