(Oracle) Compile Invalid Public Synonyms

http://www.oncalldba.com.au/node/37

 

Compile Invalid Public Synonyms

22. April 2010 - 9:40 — Scott Hollows

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'
);