What is Oracle Synonym?

 This section introduces you to Oracle synonyms that help you create aliases for schema objects such as tables, views, materialized views, sequences, procedures, and stored functions.

Synonyms provide a level of security by hiding the name and owner of a schema object such as a table or a view. On top of that, they provide location transparency for remote objects of a distributed database.

Synonyms create a level of abstraction of the underlying schema objects so that you can rename and move of the underlying objects without affecting the applications based on the synonyms. Note that synonyms themselves are not secured. When you grant object privileges on a synonym, you are granting privileges on the underlying object, and the synonym only acts as an alias in the GRANT statement.

Synonyms can be public or private. A public synonym is accessible to every user in a database and owned by a specified group named PUBLIC while a private synonym is stored in a specific schema owned by a specific user and available only to that user.

• Create synonym – show you how to create a new synonym for a table.
• Drop a synonym – describe how to drop a synonym from the database.

In a distributed database system, synonyms help simplify SQL statements.

Suppose you have a table called sales in the schema owned by the user, and you are granted the SELECT privilege for the sales table to PUBLIC.

To query data from the sales table, you use the following statement:

SELECT * FROM lion.sales;
Code language: SQL (Structured Query Language) (sql)

Notice that you must include the name of the schema and the table name to in the query.

To simplify this query, you can create a public synonym using the following CREATE PUBLIC SYNONYM statement:

CREATE PUBLIC SYNONYM sales FOR lion.sales; 
Code language: CSS (css)

Now, you can query the table sales with a simpler SQL statement:

SELECT * FROM sales; 

Notice that the sales public synonym hides the name of the sales table and its schema lion.