How to List SYNONYMS that no longer point to a valid object?

Question

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.

score 1 · Answer 1 · edited Jan 12 '15 at 12:20

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' ;

score 1 · Accepted Answer · answered Jan 08 '15 at 20:04

I couldn't use the option suggested by BriteSponge because the value of the COLUMN Status in ALL_OBJECTS doesn't seem reliable. I could find a SYNONYM that had status VALID but the object it was pointing to didn't exist.

The list of Objects I want to update is the OBJECTS_TO_UPDATE table. I could list all the synonyms from the OBJECTS_TO_UPDATE table that were pointing to an object that no longer existed with the following query:

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

How to List SYNONYMS that no longer point to a valid object?

2 Answers2