Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

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 describe scripts that will let you list synonym definitions.

The SHOW_SYN.SQL script

The following SQL*Plus script lists the definition of the synonym that you specify as an argument. If a matching public synonym exists, it will be listed as well. The results are sorted by owner and synonym name, and the owner column is decoded in such a way as to make PUBLIC sort first.

--DESCRIPTION
--This script displays the definition of a synonym.
--
--USAGE
--        @SHOW_SYN [owner.]synonym_name
--  
SET ECHO OFF 
SET VERIFY OFF
SET FEEDBACK OFF
 
--Dissect the input argument, and get the owner name and
--synonym name into two separate substitution variables.
--The owner name defaults to the current user.
SET TERMOUT OFF
DEFINE s_owner_name = ' '
DEFINE s_synonym_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN synonym_name NOPRINT NEW_VALUE s_synonym_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
         UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the table name was passed in.*/
         UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) synonym_name
  FROM dual;          
CLEAR COLUMNS
SET TERMOUT ON

--Display information about the synonym
--Information is displayed in three lines to accommodate the 
--longest possible values for each data element.
--The matching public synonym, if one exists, is 
--always shown as well.
SET HEADING OFF
SET NEWPAGE NONE
SET PAGESIZE 0
SET RECSEP OFF
SET LINESIZE 80
COLUMN owner NOPRINT
COLUMN synonym_name NOPRINT
COLUMN syn_def FORMAT A80 word_wrapped
PROMPT
SELECT DECODE(owner,'PUBLIC','1' || owner,'2' || owner) owner,
       synonym_name,
       owner || '.' || synonym_name 
       || ' synonym for ' || table_owner || '.' || table_name
       || DECODE(db_link,NULL,NULL,CHR(10) || 'at ' || db_link) syn_def
  FROM all_synonyms
 WHERE owner = '&&s_owner_name'
   AND synonym_name = '&&s_synonym_name'
UNION
SELECT DECODE(owner,'PUBLIC','1' || owner,'2' || owner) owner, 
       synonym_name,
       synonym_name 
       || ' PUBLIC synonym for ' || table_owner || '.' || table_name
       || DECODE(db_link,NULL,NULL,CHR(10) || 'at ' || db_link) syn_def
  FROM all_synonyms
 WHERE owner = 'PUBLIC'
   AND synonym_name = '&&s_synonym_name'
ORDER BY owner,synonym_name;
 
--Reset everything back to its default.
CLEAR COLUMNS
UNDEFINE s_owner_name
UNDEFINE s_synonym_name
SET HEADING ON
SET NEWPAGE 1
SET PAGESIZE 14
SET VERIFY ON
SET RECSEP WRAPPED
SET FEEDBACK ON

Running the SHOW_SYN.SQL script

Here are the results of executing the SHOW_SYN script to describe the ALL_TABLES synonym:

SQL> @show_syn all_tables

ALL_TABLES PUBLIC synonym for SYS.ALL_TABLES
JEFF.ALL_TABLES synonym for SYS.ALL_TABLES

As you can see, ALL_TABLES is a public synonym pointing to SYS.ALL_TABLES. In this case, the current user has a private synonym of the same name. This private synonym also points to SYS.ALL_TABLES.

The LIST_SYN script

In addition to seeing the definition for one synonym, you may also want to see all synonyms owned by a particular user. The following script will let you do that:

--DESCRIPTION
--List synonyms owned by the current user, or owned by a specified user.
--The user is prompted for an owner name. If an owner is not specified, 
--i.e., the user just presses ENTER, then synonyms are listed for 
--the current user.

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET DEFINE ON
SET HEADING OFF

--Ask the user for the owner.
ACCEPT username CHAR PROMPT 'List synonyms for user: '
PROMPT

--Set up the title to print the owner.
TTITLE LEFT 'SYNONYMS OWNED BY: ' owner SKIP 1
COLUMN owner NOPRINT NEW_VALUE owner

  --List synonyms owned by the user specified
  --Information is displayed in three lines to accommodate the 
  --longest possible values for each data element.
  COLUMN owner NOPRINT
  COLUMN synonym_name NOPRINT
  COLUMN db_link FORMAT A60 WRAPPED
  PROMPT
  SELECT owner,
         synonym_name,
         '      ' || owner || '.' || synonym_name 
         || ' synonym for ' || table_owner || '.' || table_name
         || DECODE(db_link,NULL,NULL,CHR(10) || 'at ' || db_link) syn_def
    FROM all_synonyms
   WHERE owner = DECODE('&&username',NULL,USER,UPPER('&&username'))
  ORDER BY synonym_name;

--Clean up: undefine vars, clear columns, change settings back to defaults.
UNDEFINE username
UNDEFINE owner
COLUMN owner CLEAR
TTITLE OFF
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON

One thing you should be aware of when looking at synonyms is that unless you are the database administrator, you won’t really be able to look at all synonyms owned by other users. The ALL_SYNONYMS view shows you three types of 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 happened to have access to all the objects in the database.

Running the LIST_SYN.SQL script

Here is a sample execution of the LIST_SYN script that shows all the public synonyms:

SQL> @list_syn
List tables for user: public

SYNONYMS OWNED BY: PUBLIC
      PUBLIC.ALL_ALL_TABLES synonym for SYS.ALL_ALL_TABLES
      PUBLIC.ALL_ARGUMENTS synonym for SYS.ALL_ARGUMENTS
      PUBLIC.ALL_CATALOG synonym for SYS.ALL_CATALOG
      PUBLIC.ALL_CLUSTERS synonym for SYS.ALL_CLUSTERS
      ...

The next example shows LIST_SYN being used to list all synonyms owned by the current user:

SQL> @list_syn
List synonyms for user: 

SYNONYMS OWNED BY: JEFF
      JEFF.ALL_CONSTRAINTS synonym for SYS.ALL_CONSTRAINTS
      JEFF.ALL_INDEXES synonym for SYS.ALL_INDEXES
      JEFF.ALL_TABLES synonym for SYS.ALL_TABLES

You could also use LIST_SYN to see a list of synonyms owned by some other users that reference objects to which you have access. Just run the script, and enter the username of interest.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Synonyms

Create new playlist

Sign In

Sign Up