How to List SYNONYMS that no longer point to a valid object? I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist.

I want to create a query to list them. But I am stuck.

My table OBJECTS_TO_UPDATE has these columns:


OBJECT_NAME
OBJECT_TYPE
OWNER


In words what I want to do goes along these lines:


I filter my OBJECTS_TO_UPDATE table by OBJECT_TYPE = 'SYNONYM'. 
Using the Owner and Name I can get the SYNONYM in ALL_SYNONYMS.
Using the TABLE_OWNER and TABLE_NAME and search for it in the dba_objects, if the object doesn't exist, then the synonym is no longer valid.


I want to write a query using all those conditions but I am having issues.

select *
from OBJECTS_TO_UPDATE o2u 
where o2u.OBJECT_TYPE = 'SYNONYM' and NOT EXISTS ( 
                            --select * from dba_objects dobj
                            --where dobj.OWNER
                            select * from ALL_SYNONYMS syn
                            where  syn.OWNER = o2u.OWNER and syn.SYNONYM_NAME = o2u.OBJECT_NAME)


I am stuck/confused about what to do inside the NOT EXISTS.
How can I achieve what I need? I have DBA privs.

Also, is this the best way to achieve what I want? Maybe there are some other views I should use that more direct.
You can query the ALL_OBJECTS view for object_type = 'SYNONYM' and status != 'VALID'.
However all_objects status is set based on the status of the object that it is referring to. Synonyms, which aren't automatically invalidated if the underlying table disappears. So the STATUS column of ALL_OBJECTS is not reliable to use.

Try using this query:

SELECT obj.owner, obj.object_type, syn.synonym_name, tab.table_name
    FROM objects_to_update obj 
        INNER JOIN all_synonyms syn 
            ON obj.object_name = syn.synonym_name 
        INNER JOIN all_tables tab 
            ON  tab.table_name = syn.table_name 
            AND tab.owner = syn.table_owner 
    WHERE obj.object_type = 'SYNONYM' ;