EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle Synonyms

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle Synonyms

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,217 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

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

Oracle Training (14 Courses, 8+ Projects)

14 Online Courses

8 Hands-on Projects

120+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More