EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Synonyms
 

Oracle Synonyms

Priya Pedamkar
Article byPriya Pedamkar

Updated February 10, 2023

Oracle Synonyms

 

 

Introduction to Oracle Synonyms

Oracle Synonym can be defined as the word itself literary means that it is actually are aliases for referencing data objects which include tables, sequences, stored functions, and stored procedures along with many other objects which can be stored in the database basically granting permission to access the data object of this schema by the user of another schema without the user having any worry of the permissions required to access the data object.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to create Synonyms in Oracle?

As we have discussed in the earlier section of the article about what is an Oracle SYNOBYM. Let us now discuss how we can create a synonym. At first, let us look at the syntax for the creation of a SYNONYM.

Syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name
FOR schema.object_name;

Parameters

  • REPLACE: This parameter is used to recreate the same synonym if it already exists; the user is not required to drop the existing synonym.
  • PUBLIC: This parameter as the name suggests is used to create a synonym that is public. The term public means that the synonym is applicable or accessible to all users. One important point to remember is that the privileges should already be present with the user for the objects to make it public.
  • schema: This parameter is used to give the schema name. If we omit this parameter then the oracle will think it to be the user’s own schema.
  • object_name: This parameter refers to the name of the object for which we are creating the schema. These objects can be table, view, sequence, function, and stored procedure.

Let us now go through an example to understand better. In our example we will create a synonym for the table employee which is present in the schema ‘nil’ and after creating the schema we will try to access it from a different schema. Let us first write a query to create a schema for the table employee.

Code #1

CREATE PUBLIC SYNONYM employee_nil
FOR nil.employee;

In the above query, the synonym name is employee_nil. After we have created the synonym, now we can use the name employee_nil instead of an employee to access it as it is also created as public. Let us write a query as now we o not have to prefix the schema name with the table name. Let us now execute the query in SQL developer and look at the result.

Output:

Oracle Synonyms - 1

As per the screenshot, we can see that the synonym employee_nil has been created.

Code #2

SELECT * FROM employee_nil;

As per our discussion in the above section, this query when executed in the SQL developer should return us the result set of the employee table. Let us execute and check the result.

Output:

Oracle Synonyms - 2

As we can see in the above screenshot that the query returns the rows of the employee table.

How to Drop Synonyms in Oracle?

In the previous section of the article, we created the synonym using the CREATE SYNONYM statement. Now as we are aware that if we can create a synonym of an object then in the due course we may need to drop the same synonym. In this section of the article we will discuss how we can drop an already created synonym. Let us first check the SYNTAX for dropping the synonym.

Syntax

DROP [PUBLIC] SYNONYM schema . synonym_name [force];

Parameters

  • PUBLIC: As the name suggests, it allows the user to drop any PUBLIC synonym. One more important point is that we do not need to use the schema name as a prefix as we have already used PUBLIC.
  • synonym_name: As the name suggests it refers to the name of the synonym.
  • Force: If we use this parameter at the end of the query then the oracle database will drop the synonym forcibly even it has dependencies. We should not use this often as it may cause later validation issues.

Let us now go through an example to understand this concept better. Now, in the previous section of this article, we had created a PUBLIC SYNONYM called employee_nil. We are going to delete the same PUBLIC SYNONYM in this section using the DROP SYNONYM statement. Let us have a look at the query.

Code:

DROP PUBLIC SYNONYM employee_nil;

As we can see that we have not used the schema name in the query because since it is already created as a public schema so it is not required. We have also not used the force parameter in the query since it does not have any dependencies on any other data objects. It is also advised not to use the force parameters often. Let us now execute the query in SQL developer and check the result.

Output:

Oracle Synonyms - 3

As we can see in the above screenshot the Public synonym EMPLOYEE_NIL has been dropped.

Advantages of Using Oracle Synonym

In the above two sections, we discussed how to create and drop the Oracle synonyms. Now, in this section, we will look into the advantages of using Oracle Synonym.

So, let us look at the few advantages below.

  • In the earlier part of the article, we discussed that the synonym can be called as an alias so synonyms actually help us to shorten the names of the lengthy table names especially from different schemas as in that we need to write the whole schema name which itself can be lengthy and complicated.
  • It helps in backward compatibility which means that if there is a table in a legacy application and we rename that table but do not the current applications using it to get hampered. In that case, we can create a synonym that has the same name as the old table name.

Conclusion

In this article we have discussed the definition of Oracle Synonyms and then later we discussed in detail along with examples about how to create a synonym and then also drop the synonym. We also discussed the various advantages that we can get with the oracle synonym.

Recommended Articles

This is a guide to Oracle Synonyms. Here we discuss an introduction to Oracle Synonyms, how to create drop synonyms with examples for understanding better. You can also go through our other related articles to learn more –

  1. Oracle COMMIT 
  2. Oracle Self Join
  3. Oracle WILDCARDS
  4. Function in Oracle

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW