O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required