Synonyms in Oracle SQL

In Oracle SQL, synonyms are database objects that serve as alias names for other database objects like tables, views, sequences, stored procedures, or even other synonyms. They allow you to access objects with simpler or alternative names without modifying the underlying object.

Key Points About Synonyms:

1. Purpose:

Synonyms are used to simplify database object access, making it easier to reference objects by different names.

They can be useful for improving security, as the original object name can be hidden behind a synonym.

Synonyms allow applications to be more flexible and portable, as you can create synonyms in different schemas or databases without changing the actual queries.

1. Types of Synonyms:

Private Synonym: A synonym that exists only within the schema in which it is created. It is only accessible by the schema owner.

Public Synonym: A synonym that can be accessed by any user in the database. This type is typically used for shared objects.

1. Basic Syntax for Creating a Synonym:

Private Synonym:

CREATE SYNONYM synonym_name FOR schema_name.object_name;

Public Synonym:

CREATE PUBLIC SYNONYM synonym_name FOR schema_name.object_name;

This allows any user to reference the object with the synonym name.

1. Example: Let's say you have a table employees in the schema hr and want to create a synonym for it:

CREATE SYNONYM emp FOR hr.employees;

Now, you can access the employees table using the synonym emp:

SELECT * FROM emp;

1. Dropping a Synonym: To remove a synonym, you can use the DROP SYNONYM command:

Private Synonym:

DROP SYNONYM synonym_name;

Public Synonym:

DROP PUBLIC SYNONYM synonym_name;

1. Synonyms for Other Database Objects: Synonyms can be created for a variety of database objects:

Tables:

CREATE SYNONYM my_table FOR hr.employees;

Views:

CREATE SYNONYM my_view FOR hr.employee_view;

Sequences:

CREATE SYNONYM my_seq FOR hr.emp_sequence;

Stored Procedures/Functions:

CREATE SYNONYM my_proc FOR hr.emp_proc;

1. Advantages of Using Synonyms:

Simplified Naming: Synonyms can be used to shorten long object names or provide more meaningful names to users.

Security: You can hide the underlying object names, limiting direct access to the original schema objects.

Decoupling: Synonyms can help decouple applications from changes to object names or locations, allowing easier migration or renaming without affecting code.

1. Use in Distributed Databases: Synonyms are often used in distributed database environments to reference remote objects in other databases without explicitly mentioning the remote database name or schema.

CREATE SYNONYM remote_emp FOR remote_db.hr.employees@db_link;

Example Use Case:

If you have a table called order_details in the sales schema, and you want all users to refer to it with a simpler name, you could create a public synonym:

CREATE PUBLIC SYNONYM orders FOR sales.order_details;

Now, any user can query the orders table as if it were in their schema, without needing to specify the sales schema:

SELECT * FROM orders;

Key Interview Insights:

Purpose and Usage: Emphasize that synonyms provide an abstraction layer that makes database access easier and more flexible. You can hide complex object names and simplify querying, especially in large or distributed databases.

Types: Clarify the difference between private and public synonyms. Public synonyms are accessible by all users, whereas private synonyms are only available within the schema that creates them.

Security Considerations: In interviews, you could also discuss how synonyms help with security by hiding the true names of tables and limiting access to the underlying objects.

By focusing on these points, you'll be able to clearly articulate the importance and practical usage of synonyms in Oracle SQL.