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.
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 = '' AND synonym_name = '
username
'
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 = '' AND synonym_name = '
username
' UNION SELECT * FROM all_synonyms WHERE synonym_owner = 'PUBLIC' AND synonym_name = '
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.
The following sections describe scripts that will let you list synonym definitions.
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
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.
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.
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.