Create and Drop Synonym Tips in Oracle with Examples

Hi,

I will explain Create Synonym Tips in Oracle with Examples in this post.

Synonym is an object that points to a specific object in the Oracle database. There are lots of database objects which names are very long and complex. You can create Synonym for these objects and use the Synonym instead of its original name.

For example; I am using the DBA_TABLES views and want to create a Synonym for it. When I create any Synonym for it, I can use it instead of DBA_TABLES views.

Synonym is created as follows.

SQL> CREATE SYNONYM MEHMET.TABLES FOR DBA_TABLES;

Synonym created.

SQL>

Use Synonym instead of DBA_TABLES views.

SQL> SELECT COUNT(*) FROM MEHMET.TABLES;

COUNT(*)
----------
22948

Run count query for the DBA_TABLES views, you can get the same result.

SQL> SELECT COUNT(*) FROM DBA_TABLES;

COUNT(*)
----------
22948

SQL>

This Synonym is Private Synonym and Only Mehmet User can see and use it.

If you want it as Public you can create it public as follows.

SQL> CREATE PUBLIC SYNONYM ASH FOR V$ACTIVE_SESSION_HISTORY;

Synonym created.

SQL>

I have created ASH named Public Synonym for V$ACTIVE_SESSION_HISTORY, All developers can use this synonym instead of V$ACTIVE_SESSION_HISTORY view.

SQL> select count(*) from V$ACTIVE_SESSION_HISTORY;

COUNT(*)
----------
269236

SQL> select count(*) from ASH;

COUNT(*)
----------
269236

SQL>

You can drop Synonym as follows.

SQL> drop synonym ASH;
drop synonym ASH
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

This error is related with public synonym, drop it as follows.

SQL> DROP PUBLIC SYNONYM ASH;

Synonym dropped.


SQL> 
SQL> drop synonym MEHMET.TABLES;

Synonym dropped.

SQL>

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-oracle-dba-tutorials-for-beginners-junior-oracle-dba/