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/

 773 views last month,  8 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!