Oracle Database 11g -- Compile All Invalid Synonyms

Problem

You need to compile all invalid synonyms in your database

Solution

Use the following script to generate another script which being executed will validate all invalid synonyms in the database

SELECT    'create or replace '
       || DECODE (OWNER, 'PUBLIC', 'PUBLIC', '')
       || ' synonym '
       || DECODE (owner, 'PUBLIC', '', CONCAT ('' || owner, '.'))
       || synonym_name
       || ' for '
       || table_owner
       || '.'
       || table_name
       || ';'
  FROM dba_synonyms
 WHERE (owner, synonym_name) IN
          (SELECT owner, object_name
             FROM dba_objects
            WHERE status <> 'VALID' AND object_type = 'SYNONYM');