Oracle SQL - Table Synonyms

Introduction

You can use the CREATE SYNONYM command to create synonyms for tables or views.

Once created, you can use synonyms in all your SQL commands instead of "real" table and view names.

Synonyms are useful when accessing tables from different schemas, not owned by yourself.

Without synonyms, you must explicitly prefix those object names with the schema name and a period.

By using synonyms, your applications don't need to contain explicit schema names.

Oracle supports public and private synonyms.

By default, synonyms are private. You need to specify the PUBLIC keyword to create public synonyms.

All database users can use public synonyms, but you need DBA privileges to be able to create them.

The following code shows how you can create a synonym, how the synonym shows up in the data dictionary views CAT and USER_SYNONYMS, and how you can drop a synonym.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w ww.  j  av a  2s.  c  om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> create synonym e for emp;

Synonym created.

SQL>
SQL> describe e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(8)
 INIT                                      NOT NULL VARCHAR2(5)
 JOB                                                VARCHAR2(8)
 MGR                                                NUMBER(4)
 BDATE                                     NOT NULL DATE
 MSAL                                      NOT NULL NUMBER(6,2)
 COMM                                               NUMBER(6,2)
 DEPTNO                                             NUMBER(2)

SQL>
SQL> select * from cat where table_name='EMP';

TABLE_NAME                     | TABLE_TYPE
------------------------------ | -----------
EMP                            | TABLE

SQL>
SQL> select synonym_name, table_owner, table_name
  2  from   user_synonyms;

SYNONYM_NAME                   | TABLE_OWNER
------------------------------ | ------------------------------
TABLE_NAME
------------------------------
DEF$_AQCALL                    | SYSTEM
DEF$_AQCALL


SYNONYM_NAME                   | TABLE_OWNER
------------------------------ | ------------------------------
TABLE_NAME
------------------------------
E                              | SYS
EMP


10 rows selected.

SQL>
SQL> drop synonym e;

Synonym dropped.

SQL>

• Previous
• Next