I have spent the past fortnight trying to clean up several thousand invalid objects on the production databases. The majority of these invalid objects were synonyms.
This is utterly incredible! I didn’t even try to find the cause – I just wrote a script to rebuild the synonyms:
Updated for database links and correction for synonyms of public synonyms!
'CREATE OR REPLACE' ||
DECODE( owner, 'PUBLIC', ' PUBLIC ', ' ' ) ||
DECODE( owner, 'PUBLIC', ' ', ' ' || TRIM( owner ) || '.' ) ||
' FOR ' ||
DECODE( table_owner, NULL, ' ', 'PUBLIC', ' ', ' ' || TRIM( table_owner ) || '.' ) ||
TRIM( table_name ) ||
DECODE( syn.db_link, NULL, ';', '@' || TRIM( syn.db_link ) || ';' )
( owner, synonym_name ) IN (
status = 'INVALID'
object_type = 'SYNONYM'