- Synonym can be used to hide the real name and owner of an object.
- It provides public access to an object.
- It also provides location transparency for tables, views or program units of a remote database.
- It simplifies the SQL statements for database users.
In Oracle, synonyms are used for the following purposes:
- Simplifying Object References: Synonyms provide an alternative name for a table, view, sequence, procedure, or other schema objects. This can simplify the SQL statements and make them more readable.
- Security: Synonyms can be used to hide the underlying structure of the database. Users can be granted access to a synonym instead of the actual table or view, providing an additional layer of security.
- Ease of Object Management: If the structure or location of a table or view changes, you can update the synonym to point to the new location or structure without changing the application code that references the synonym.
- Cross-Database Access: Synonyms can be used to access objects in another schema or even another database, making it easier to work with distributed databases or multiple schemas.
For example, if you have a table named “employees” in the HR schema, you can create a synonym like this:
CREATE SYNONYM emp FOR hr.employees;
Then, users can refer to the table as “emp” instead of “hr.employees.”
SELECT * FROM emp;
This improves readability and allows for easier maintenance if the table location or structure changes.