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';


------------------------------ ------------------------------



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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

show all menu

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: