Mastering Oracle- A Comprehensive Guide to Modifying Synonyms in Oracle Databases

How to Alter Synonym in Oracle

In the world of Oracle databases, synonyms play a crucial role in simplifying the access to underlying objects. A synonym is essentially an alias for a database object, such as a table, view, or procedure. It allows users to access the object using a more intuitive or friendly name, rather than the complex object name. However, there may be instances where you need to alter a synonym to reflect changes in the underlying object or to provide better access control. In this article, we will discuss the steps to alter a synonym in Oracle.

Understanding Synonyms in Oracle

Before diving into the process of altering a synonym, it is essential to understand the basics of synonyms in Oracle. A synonym is created using the CREATE SYNONYM statement and can be owned by a user or a role. When a synonym is created, it points to the actual object in the database. For example, if a user creates a synonym for a table named “EMPLOYEES”, they can access the table using the synonym name instead of the actual table name.

Steps to Alter a Synonym in Oracle

To alter a synonym in Oracle, follow these steps:

1. Connect to the Oracle database using SQLPlus or any other database client.
2. Use the SQL statement to alter the synonym. The syntax for altering a synonym is as follows:

“`sql
ALTER SYNONYM synonym_name RENAME TO new_synonym_name;
“`

Replace `synonym_name` with the current name of the synonym and `new_synonym_name` with the desired new name for the synonym.

3. Execute the SQL statement to alter the synonym. If the alteration is successful, you will see a message indicating that the synonym has been altered.

Example: Altering a Synonym

Let’s consider an example to illustrate the process of altering a synonym. Suppose a user named “john” has created a synonym named “EMPLOYEES_SYN” for the “EMPLOYEES” table. Now, the user wants to rename the synonym to “EMPLOYEES_NEW_SYN”.

“`sql
ALTER SYNONYM EMPLOYEES_SYN RENAME TO EMPLOYEES_NEW_SYN;
“`

After executing the above SQL statement, the synonym “EMPLOYEES_SYN” will be renamed to “EMPLOYEES_NEW_SYN”.

Conclusion

Altering a synonym in Oracle is a straightforward process that involves using the ALTER SYNONYM statement. By following the steps outlined in this article, you can easily rename or modify a synonym to suit your requirements. Remember that altering a synonym does not affect the underlying object; it only changes the alias used to access the object.