Updated February 10, 2023
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.
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:
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:
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:
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 –
