jeudi 8 janvier 2015

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 and 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?


Also, is this the best way to achieve what I want? Maybe there are some other views I should use that more direct.





Aucun commentaire:

Enregistrer un commentaire