Synonyms in Oracle with examples

In Oracle, a synonym is an alias for a database object such as a table, view, sequence, procedure, or another synonym. Synonyms provide a way to simplify SQL statements and hide the underlying object details from users. They can be particularly useful for simplifying complex object names, managing access to objects across different schemas, and enhancing security by hiding the actual object owner.

Types of Synonyms

1. Private Synonyms: These are created within a specific schema and are accessible only to users with appropriate privileges within that schema.
2. Public Synonyms: These are created in the public schema and are accessible to all users within the database.

Creating Synonyms

1. Creating a Private Synonym

CREATE SYNONYM synonym_name FOR schema.object_name;

2. Creating a Public Synonym

CREATE PUBLIC SYNONYM synonym_name FOR schema.object_name;

Examples

1. Private Synonym Example

Create a private synonym for a table in the same schema:

Assume you have a table employees in the hr schema.

CREATE SYNONYM emp FOR hr.employees;

Now, you can query the employees table using the synonym emp.

SELECT * FROM emp;

2. Public Synonym Example

Create a public synonym for a table in another schema:

Assume you have a table departments in the hr schema, and you want to make it easily accessible to all users.

CREATE PUBLIC SYNONYM dept FOR hr.departments;

Now, all users can query the departments table using the synonym dept.

SELECT * FROM dept;

3. Synonym for a View

Create a synonym for a view:

Assume you have a view employee_view in the hr schema.

CREATE SYNONYM emp_view FOR hr.employee_view;

Now, you can query the employee_view using the synonym emp_view.

SELECT * FROM emp_view;

4. Synonym for a Procedure

Create a synonym for a procedure:

Assume you have a procedure calculate_salary in the hr schema.

CREATE SYNONYM calc_sal FOR hr.calculate_salary;

Now, you can execute the procedure using the synonym calc_sal.

EXECUTE calc_sal;

Managing Synonyms

1. Dropping a Synonym

Drop a private synonym:

DROP SYNONYM emp;

Drop a public synonym:

DROP PUBLIC SYNONYM dept;

2. Replacing a Synonym

Use the OR REPLACE clause to replace an existing synonym:

CREATE OR REPLACE SYNONYM emp FOR hr.new_employees;

Practical Use Cases

1. Simplifying SQL Statements: Synonyms can simplify SQL statements by providing shorter or more meaningful names for objects.
2. CREATE SYNONYM emp FOR hr.employees;
3. SELECT * FROM emp;

1. Hiding Implementation Details: Synonyms can hide the underlying schema or object details from end-users, improving security and abstraction.

   CREATE PUBLIC SYNONYM user_data FOR secure_schema.encrypted_user_data;
   SELECT * FROM user_data;
   

2. Cross-Schema Access: Synonyms allow easy access to objects across different schemas without requiring users to prefix the schema name.

   CREATE SYNONYM remote_table FOR remote_schema.table_name;
   SELECT * FROM remote_table;
   

3. Migrating Objects: During database migrations or changes, synonyms can help minimize the impact on applications by providing a consistent object name.

   CREATE OR REPLACE SYNONYM app_table FOR new_schema.new_table;

Conclusion

Synonyms in Oracle are powerful tools that provide alias names for database objects, simplifying SQL statements, enhancing security, and facilitating easier cross-schema access. By using synonyms, you can abstract and manage underlying object details more effectively, leading to cleaner and more maintainable database applications.