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 byOBJECT_TYPE = 'SYNONYM'
. - Using the Owner and Name I can get the
SYNONYM
inALL_SYNONYMS
. - Using the
TABLE_OWNER
andTABLE_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.
Best Answer
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: