Back to articles list
Synonyms are a very powerful feature of Oracle. They are auxiliary names that relate to other database objects: tables, procedures, views, etc. They work like Unix hard links; a pointer to point to an object that exists somewhere else.
Synonyms can be created as PRIVATE (by default) or PUBLIC.
- public synonyms are available to all users in the database.
- private synonyms exist only in specific user schema (they are available only to a user and to grantees for the underlying object)
The syntax for a synonym is as follows:
CREATE [PUBLIC] SYNONYM synonym_name FOR object_name
Example
Assume we have a database with a schema called ABC. This schema contains a table called ORDERS.
To query the table, you use the following SELECT statement:
SELECT * FROM ABC.ORDERS;
To enable a synonym, we must first grant select privileges to all database users:
GRANT SELECT ON ABC.ORDERS TO PUBLIC;
Next, we create a public synonym for the table:
CREATE PUBLIC SYNONYM ORDERS_DATA FOR ABC.ORDERS;
From now on, anyone can query your table using the synonym:
SELECT * FROM ORDERS_DATA;
In Oracle you can refer to synonyms in the following statements:
- select
- insert
- update
- delete
- flashback table
- explain plan
- lock table
When to Use Synonyms
As database systems grow and applications improve, there is usually a need to change the names of tables and views to better reflect their new functionality. For example, a stored procedure named ADD_CLIENT might be used to verify credit limits as well as to add a new client. Therefore, it might be natural to change the name of the procedure to something like VERIFY_NEW_CLIENT. The problem comes in changing the name in all places before placing the revised application into production. Thanks to synonyms, both names might be used in the application and database.
Synonyms for Synonyms
A curious feature of a synonym is that each may have its own synonym or many synonyms. Let's assume that we have a table with a formal name such as REL001_PURCHASE_TABLE, a simple name to allow easier querying PURCHASE_TABLE, and a lazy name, PT, for people who prefer to type fewer characters. PURCHASE_TABLE and PT could be defined as synonyms for the base table:
create synonym PURCHASE_TABLE for table REL001_PURCHASE_TABLE; create synonym PT for table REL001_PURCHASE_TABLE;
or as a chain, where PURCHASE_TABLE is a synonym referring to REL001_PURCHASE_TABLE as well being referred by synonym PT (each refer to previous definition).
create synonym PURCHASE_TABLE for REL001_PURCHASE_TABLE; create synonym PT for PURCHASE_TABLE;
The advantage of creating synonyms as chains is that an alter synonym statement can change PURCHASE_TABLE and the change will automatically be reflected by all synonyms up the chain.
Oracle enables access to synonyms by:
- ALL_SYNONYMS describes the synonyms accessible to the current user
- DBA_SYNONYMS describes all synonyms in the databases
- USER_SYNONYMS describes the private synonyms (synonyms owned by the current user)
Naming Synonyms
According to the picture above: Public synonyms are non-schema objects, when private synonyms as tables are schema objects.
- USER, ROLE and PUBLIC SYNONYM are in their own collective namespace.
- TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM have their own unique namespace.
- INDEXes have their own unique namespace.
- CONSTRAINTs objects have their own unique namespace within a given schema
While the objects don't share the same namespace, you can give them the same names.
Remember, that you can have:
- a table and public synonym with the same name
- a public synonym and a private synonym with same name
You cannot have:
- a table and a private synonym with the same name inside the same schema.
Benefits of Synonyms
What are the benefits of using a synonym in database development over a view?
This is a common design question, that's why it's important to keep in mind the following major points when choosing between a synonym and a view.
A view takes the output of a query and treats it as a table. Therefore a view can be thought of a stored query or a virtual table. Views have many similarities to tables. For example, you can define views with up to 1000 columns, just like a table. They are used to:
- Hide some columns (if you want users to see only specific columns or only specific rows, then you can create a view of that table for other users to access).
- You can:
- query views
- update
- insert into
- delete from views.
All operations performed on a view actually affect data in the base table of the view.
You cannot explicitly define triggers on views, but you can define them for the underlying base tables referenced by the view.
- They are used to create alias to an object in other databases.
- Oracle Database lets you create synonyms so that you can hide the database link name from the user
- Synonyms provide easy to use names for remote tables, i.e database links
- It is a good solution for staging mock tables when testing. For example, when you want to test a small subset of data in a huge table. Thanks to synonyms, you can re-direct the source table to a smaller table that you can test. The benefit of such a situation is that you can update/delete the data in the mock table without affecting your source table
- Public synonyms allow you to reference an object without the need for the schema.object syntax, making the reference more readable.
- Synonyms are used when a DBA wishes to separate database objects in different schemas, but wants/need some of these objects to be visible to other schemas without giving direct access to them.
- Public synonyms enable an object to be referenced by every user – assuming they have the appropriate privileges
- Public synonyms also increase the chance of name collisions if you ever decide to consolidate databases
- In the case of a table, index behavior is identical to that of the base object. When execution plans are generated, the same plan is generated irrespective of using the table name or corresponding synonym.
- Synonyms themselves don't enable safety: when you grant object privileges on a synonym, you are really granting privileges on the underlying object, and the synonym is acting only as an alias for the object in the GRANT statement.
Further Reading
Here are some other resources where you can read more about SQL synonyms:
