Hate Synonyms


I have spent the past fortnight trying to clean up several thousand invalid objects on the production databases.  The majority of these invalid objects were synonyms.

This is utterly incredible!  I didn’t even try to find the cause – I just wrote a script to rebuild the synonyms:

Updated for database links and correction for synonyms of public synonyms!

SELECT
    'CREATE OR REPLACE' ||
    DECODE( owner, 'PUBLIC', ' PUBLIC ', ' ' ) ||
    'SYNONYM' ||
    DECODE( owner, 'PUBLIC', ' ', ' ' || TRIM( owner ) || '.' ) ||
    synonym_name ||
    ' FOR ' ||
    DECODE( table_owner, NULL, ' ', 'PUBLIC', ' ', ' ' || TRIM( table_owner ) || '.' ) ||
    TRIM( table_name ) ||
    DECODE( syn.db_link, NULL, ';', '@' || TRIM( syn.db_link ) || ';' )
      AS cmd
  FROM
    dba_synonyms
  WHERE
    ( owner, synonym_name ) IN (
      SELECT
          owner,
          object_name
        FROM
          dba_objects
        WHERE
            status = 'INVALID'
          AND
            object_type = 'SYNONYM'
    )

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s