Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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 table location. All of the scripts in this chapter have used synonyms instead of table names. all_tables, for example, is actually a public synonym for the sys.all_tables table.

To find out about synonyms, you can query the all_synonyms view. Example 10-11 queries all_synonyms to find any synonyms that point to the all_tables system view.

Example 10-11. Listing synonyms referring to all_tables

SELECT owner, synonym_name
FROM all_synonyms
WHERE table_owner = 'SYS' AND table_name = 'ALL_TABLES';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC                         ALL_TABLES
GENNICK                        ALL_TABLES

There are two types of synonyms. Synonyms owned by a user are private synonyms and affect only that user. Public synonyms are owned by PUBLIC and affect all database users. Example 10-11 shows one synonym of each type. For whatever reason, the user GENNICK has created his own, private synonym pointing to all_tables. He may have done that to illustrate a point for a book.

Private synonyms override public synonym definitions, so it's important to know when both types exist. Change GENNICK's all_tables synonym to point to sys.all_object_tables, and he'd no doubt be one confused DBA the next time he went to look at his table definitions.

One thing you should be aware of when looking at synonyms is that ...