Invalid Public Synonyms  

Posted by Mawahid

This article shows how to recompile public synonyms in an Oracle database that are currently invalid
Public synonyms may show as invalid in DBA_OBJECTS even though they are actually valid and working correctly.  This often occurs after the target object is modified or dropped and recreated.  When this occurs, Oracle flags the public synonym as invalid as a precaution so it knows to re-validate / compile it next time it is used.

To view a list of invalid public synonyms ...

SELECT *
  from dba_synonyms
where owner = 'PUBLIC'
   and synonym_name in (
          SELECT object_name
            from dba_objects
           where object_type = 'SYNONYM'
             and owner  =  'PUBLIC'
             and status <> 'VALID'
             );

Dont worry too much about the invalid public synonyms as the Oracle database will automatically re-validate them and change the status to VALID when they are next accessed.  This occurs transparently so your users wont notice anything apart from the slight time delay while Oracle does its thing.
However, if you are a neat freak and dont like invalid objects in your nice clean database, you can run this script to force any invalid public synonyms to be compiled / revalidated.

    -- force invalid public synonyms to recompile by "touching" them
    -- using this query for each public synonym ...
    -- 
SELECT count(*) temp_count from <OBJECT_NAME> where 1=2
    -- note that  "1=2" is used to make the queries run very fast while still forcing the public synonyms to be validated


BEGIN
        -- loop through all invalid public synonyms and

        -- generate a SELECT statement that will "touch" the object
   FOR c IN (SELECT    'select count(*) temp_count from '
                    || '"' || synonym_name || '"'
                    || ' where 1=2' synonym_sql
               from DBA_SYNONYMS
              where owner = 'PUBLIC'
                and synonym_name in (
                       SELECT object_name        -- invalid public synonyms
                         from DBA_OBJECTS
                        where object_type = 'SYNONYM'
                          AND owner = 'PUBLIC'
                          AND status <> 'VALID')
             )
  LOOP
        -- run the "touch" query to force the public synonym to re-validate if possible
      EXECUTE IMMEDIATE (c.synonym_sql);
  END LOOP;
END;
/


   -- after the script has completed, the Public Synonyms should now be in a valid state
    -- show invalid public synonyms
set linesize 300
set pagesize 5000
column db_link format a30

SELECT *
  from dba_synonyms
where owner = 'PUBLIC'
   and synonym_name in (
          SELECT object_name
            from dba_objects
           where object_type = 'SYNONYM'
             and owner  =  'PUBLIC'
             and status <> 'VALID'
             );