Oracle: Recreate invalid synonyms

During ex-import-operations it is often the case, that related synonyms in other schemas become invalid, because of the temporary absence of the underlying objects.

Unlike other objects like procedures or functions invalid synonyms are not automatically “recompiled” on next use. So we have to recreate them one by one. To ease this task and recreate all of them in a batch we can use this piece of code, which dynamically recreates all invalid synonyms in the database:

for a in (
    select 'create or replace synonym ' || a.owner || '.' || a.synonym_name || ' for ' || a.table_owner || '.' || a.table_name 
    as cmd from dba_synonyms a where a.owner in 
    (select distinct b.owner from dba_objects b where b.object_type='SYNONYM' and b.STATUS='INVALID'))
    execute immediate a.cmd;
end loop;

If you don’t have access to the named “dba_*” views, you can simply replace “dba_” with “all_”.