Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata