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_”.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: