Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Synonym with base objects dropped

Siva OraJun 15 2011 — edited Jun 15 2011
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 13 2011
Added on Jun 15 2011
6 comments
6,928 views