Oracle SQL*Plus: The Definitive Guide

Synonyms

A synonym is an alternate name for a table. By coding your programs to use synonyms instead of table names, you insulate yourself from any changes in the name, ownership, or location of those tables. All of the scripts in this chapter have actually used synonyms instead of table names. ALL_TABLES, for example, is actually a public synonym for the SYS.ALL_TABLES table.

Looking at Synonyms

To look at a synonym, you just select information from ALL_SYNONYMS for the synonym you are interested in. The following SELECT is an example:

SELECT *
  FROM all_synonyms
 WHERE synonym_owner = '
                  username'
   AND synonym_name = '
                  synonym_name'

So far, so good. However, there are two types of synonyms: public and private. Synonyms owned by a user are private synonyms and affect only that user. Public synonyms are owned by PUBLIC and affect all database users. If you are interested in a specific synonym, you probably also want to know if both public and private versions exist. Because of that, you might modify your query to look like this:

SELECT *
  FROM all_synonyms
 WHERE synonym_owner = '
                  username'
   AND synonym_name = '
                  synonym_name'
UNION 
SELECT *
  FROM all_synonyms
 WHERE synonym_owner = 'PUBLIC'
   AND synonym_name = '
                  synonym_name'

Now you will see both synonym types, which is helpful in detecting cases where a private synonym conflicts with a public synonym. Private synonyms override public synonym definitions, so it’s important to know when both types exist.

Synonym Scripts

The following sections ...