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.
OBJECTS_TO_UPDATE has these columns:
In words what I want to do goes along these lines:
- I filter my
OBJECT_TYPE = 'SYNONYM'.
- Using the Owner and Name I can get the
- Using the
TABLE_NAMEand 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
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.