Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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