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 unless you are the DBA, you won't be able to look at all synonyms owned by other users. The all_synonyms view shows you three synonyms:

• Public synonyms (owned by PUBLIC )
• Synonyms you own
• Synonyms owned by other users that reference tables and other objects to which you have access

The only time the all_synonyms view would show you all the synonyms in the database would be if you had access to all the objects in the database.