Tables of synonyms if renamed
the synonyms will not be invalidated and will not be
displayed with the above query.

The synonyms on selection will give
ORA-00980: synonym translation is no longer valid

They need to be identified with
http://www.oracle.com/technology/ora...03/113003.html

--syn not displayed
SQL> SELECT owner, object_name, last_ddl_time, status,
2 temporary, generated, secondary
3 FROM all_objects
4 WHERE owner NOT IN( 'SYS', 'SYSTEM' ) AND
5 object_type = 'SYNONYM' AND
6 status = 'INVALID';

no rows selected

--Synonyms displayed here with the below query that are no longer
valid(for testing purpose I created
the last synonym at scott user and renamed the table.)

SQL> select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
2 'SYNONYM'||s.owner||'.')||s.synonym_name||';'
3 from dba_synonyms s
4 where table_owner not in('SYSTEM','SYS')
5 and db_link is null
6 and not exists
7 (select 1
8 from dba_objects o
9 where s.table_owner=o.owner
10 and s.table_name=o.object_name)
11 /

'DROP'||DECODE(S.OWNER,'PUBLIC','PUBLICSYNONYM','SYNONYM'||S.OWNER||'.')||
--------------------------------------------------------------------------
drop PUBLIC SYNONYM /4e6818ad_OrdSourceSvr;
drop PUBLIC SYNONYM ALL_OLAP_OS_CHILD_INSTANCES;
drop PUBLIC SYNONYM DBA_OLAP_OS_CHILD_INSTANCES;
drop PUBLIC SYNONYM ALL_OLAP_OS_OSA_PRIVILEGES;
drop PUBLIC SYNONYM DBA_OLAP_OS_OSA_PRIVILEGES;
drop PUBLIC SYNONYM CWM_OLAP_SERVER_LIST;
drop PUBLIC SYNONYM ALL_OLAP2_AW_MAP_LEVEL_USE;
drop SYNONYMSCOTT.TEST23SYN;