Let us learn Synonym In Oracle

Introduction

Synonym is an alternative name/alias assigned to the objects like views, sequences, stored procedures etc.

Syntax

Create [or Replace]  [Public]  Synonym [Schema .] Synonym_Name
For [schema .] object_name [@ dblink];

It is an alias for the following types of objects

1. Table
2. Package
3. View
4. Materialized view
5. Sequence
6. Java Class
7. Schema Object
8. Stored Procedure
9. User-defined object
10. Function
11. Synonym

Straight to Experiment

Create a Synonym

E.g. Let us create a synonym for the tblPlayers table

SQL> Create Synonym Players For tblPlayers;

Synonym Created

Then we can query the synonym as under

SQL> Select * From Players;

  PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO  DOB       FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
        11 P          Chopra     India      14-FEB-98
         1 A          Raman      India      20-DEC-84        2000
         2 B          Kadir      India      01-JAN-80        1000
         3 C          Nadir      India      21-APR-00        3000
         4 D          Das        India      11-NOV-80         500
         5 E          Punchkar   India      14-FEB-80       12000
         6 F          Fateman    USA        20-DEC-71       22000
         7 G          Gajani     USA        01-JAN-90         999
         8 H          Hariharan  USA        21-APR-99        7000
         9 I          Ikat       USA        11-NOV-88        5999
        10 J          Jwar       USA        14-FEB-98        7900

11 rows selected.

Insert record through the synonym

SQL> Insert into Players (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) 
Values(12,'Z','Khan','India',To_Date('20/12/1984','DD/MM/YYYY'),2000);

1 row created.

SQL> Select * From Players;

  PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO  DOB       FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
        11 P          Chopra     India      14-FEB-98
	12 Z          Khan       India      20-DEC-84        2000
         1 A          Raman      India      20-DEC-84        2000
         2 B          Kadir      India      01-JAN-80        1000
         3 C          Nadir      India      21-APR-00        3000
         4 D          Das        India      11-NOV-80         500
         5 E          Punchkar   India      14-FEB-80       12000
         6 F          Fateman    USA        20-DEC-71       22000
         7 G          Gajani     USA        01-JAN-90         999
         8 H          Hariharan  USA        21-APR-99        7000
         9 I          Ikat       USA        11-NOV-88        5999
        10 J          Jwar       USA        14-FEB-98        7900

12 rows selected.

In a similar way we can perform other DML operations

Drop a Synonym

SQL> Drop Synonym Players;

Synonym dropped

Conclusion

So in this article, we have seen how Oracle's Synonym works.Hope this will be helpful.Thanks for reading.