Synonym with base objects dropped
Siva OraJun 15 2011 — edited Jun 15 2011I have been asked to find the invalid state of synonymns.
There are many synonyms that exist in my database that do not have any underlying objects currently present. The reason is that when the underlying objects are deleted the corresponding synonym clean up was not performed.
The Query to find the invalid status of synonymn uses the Status != ‘INVALID’ condition to filter the invalid synonyms
select count(*) from dba_objects where object_type = 'SYNONYM' and status=’INVALID’;
The status of some of the synonyms that are invalid due to the underlying objects being deleted is showing as ‘VALID’ in the dba_objects table when they are ‘touched’ so this criteria cannot be used to get the invalid synonym list .
Is there any solution for findind the invalid state of synonyms?
Note:
I understand that unlike other objects in Oralce DB Schema, synonymn do not become invalid if the objects they point to are dropped. Also a synonyms can point to an objects that does not exist or for which the owners does not have privileges.
Edited by: Sivaprasad S on Jun 15, 2011 5:59 PM