The Complete Guide to Oracle Synonyms

In this article, I’ll explain all you need to know about Synonyms.

What is an Oracle Synonym?

A synonym is an object in a database that represents an alternative name for other objects, such as tables, views, sequences, and stored procedures.

 

Why Would you Use a Synonym?

Why would you want to create a synonym? Why not just rename the original object?

The main reason is to simplify the user access to an object. You can provide access to an object to a user using a synonym, and you don’t need to worry about who owns the original object.

It also simplifies access to objects, especially when multiple databases and schemas are involved.

The examples later in this article will demonstrate this further.

 

Oracle Synonym vs View

As mentioned above, a synonym is an alias or alternative name for an object.

How is this different from a view?

A view object is an SQL query that is saved and run when other queries use that view. It works like a table.

Why would you use a synonym instead of a view?

Well, a view contains more complicated logic. If you just want to provide an alias for a table or another object, you can use a synonym. If you want to SELECT data from a table and use WHERE conditions or other logic, then a view is better.

Also, a view can only be created on objects with data, such as tables and other views. Synonyms can be created on tables, views, sequences, stored procedures, and more.

They both have their advantages, but they should only be used when appropriate.

 

Difference Between Public and Private Synonym in Oracle

There are two types of synonyms that can be created on an Oracle database: public and private.

• Public synonym: can be accessed by any user on the database. The user who creates the synonym it does not own it – it’s owned by the PUBLIC user group.
• Private synonym: can only be accessed by the person who created the synonym. This user is also the owner. The synonym name must be unique within the schema.

What happens if you have a public synonym with the same name as an existing object? Or a private synonym?

This is a valid scenario, and Oracle has rules on which order they are processed in. Just like how, in mathematics, the multiplication and division are performed before the addition and subtraction, Oracle has an order in the way that the objects are looked up.

The order of precedence for objects and synonyms is:

1. Local schema object. Oracle will look at the user’s schema for the object name.
2. Private synonym. If a schema object is not found, Oracle will look in the private synonyms for the user.
3. Public synonym. If a private synonym is not found, Oracle will look in the public synonyms.

This is how public synonyms and private synonyms compare:

Criteria	Public	Private
Who owns it?	PUBLIC user group	The user that created it
Scope of the unique name	All database objects	The schema of the user who created it
Who can use it?	All database users	Only the user who created it
Order of precedence	Third, after schema objects and private synonyms	Second, after schema objects

Generally, it’s a good idea to avoid creating public synonyms. Sure, they can make management a bit easier, but they can pose a security risk and can make development harder, because there’s an object that exists and available to all users.

 

How to Create a Synonym in Oracle

To create a synonym in Oracle (either private or public), we use the CREATE SYNONYM command.

The syntax is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] synonym_name
FOR [schema.] object_name [@dblink_name];

The parameters for this function are:

• OR REPLACE: This is optional and allows you to replace an existing synonym name, instead of dropping it and recreating it.
• PUBLIC: Use the PUBLIC keyword to create a public synonym. If this is omitted, a private synonym is created.
• schema: This is optional and is the name of the schema you want to create the synonym in. If this is omitted, the synonym is created in your own schema. If you are creating a PUBLIC synonym, you cannot specify a schema.
• synonym_name: The name of the new synonym that is being created. It should be 30 characters or less. I say should, because names longer than 30 characters can be created and dropped, but they are transformed into a shorter string for storage.
• FOR schema: The schema is optional, but specifies the schema name that the object exists in.
• object_name: The name of the object that the synonym will relate to.
• dblink_name: This is the name of the database link if it is required.

A synonym can be created on the following types of objects:

• table
• view
• stored procedure
• function
• package
• sequence
• materialised view
• synonym
• java class schema object
• user-defined object

Let’s see some examples of creating a synonym.

Example 1 – Create private synonym

CREATE SYNONYM emp
FOR hr.employees;

This creates a new private synonym called emp, which refers to the employees object in the hr schema.

 

Example 2 – Create public synonym

CREATE PUBLIC SYNONYM cust
FOR sales.customers;

This will create a new public synonym for the customer object in the sales schema. All users can query the cust synonym and access the sales.customers table (if they have access to the underlying sales.customers table).

 

Example 3 – Same name

CREATE SYNONYM product
FOR sales.product;

This will create a private synonym. Notice that both objects have the same name. A query such as this will look for the local object first (which does not exist), and then the synonym:

SELECT * FROM product;

However, querying on the original object will also access that object:

SELECT * FROM sales.product;

If a new table was created in the user’s current schema called product, then this query will look at that table instead of the synonym:

SELECT * FROM product;

 

How to Drop a Synonym in Oracle

To remove a synonym that already exists, you can drop it from the database.

The syntax for doing this is:

DROP [PUBLIC] SYNONYM [schema.] synonym_name [FORCE];

The parameters are:

• PUBLIC: specifies whether you are dropping a private or public synonym. If you’re not sure what type your synonym is, then see the section below on finding all synonyms in a database.
• schema: This is the name of the schema that your synonym exists on. This is only needed if you are dropping a private schema.
• synonym_name: The name of the synonym to be dropped.
• FORCE: This will force Oracle to drop the synonym even if it has dependencies.

 

Let’s see some examples.

 

Example 1 – Drop a public synonym

DROP PUBLIC SYNONYM emp;

This will drop the public synonym emp.

 

Example 2 – Drop a private synonym

DROP SYNONYM customer;

This will drop the private synonym called customer.

 

Example 3 – Drop a synonym with a schema

DROP SYNONYM hr.emp;

This will drop the synonym emp that exists in the hr schema.

 

How to Find All Synonyms In the Database

So you’ve created some synonyms, or your database already has some. How can you check the synonyms in Oracle? How do you know if a synonym is public or private?

There is no “oracle list synonyms” command, but you can query some database views to find this information:

• all_synonyms: View synonyms available to the current user.
• dba_synonyms: View all synonyms in the database.
• user_synonyms: Private synonyms owned by the current user.

To find a list of synonyms on the database, you can run these queries.

SELECT * FROM all_synonyms;

This shows us a list of all synonyms available to the current user.

A query on the dba_synonyms view will show us all synonyms in the database:

SELECT * FROM dba_synonyms;

And finally, a query on user_synonyms will show us all private synonyms owned by the current user:

SELECT * FROM user_synonyms;

 

Conclusion

So, in summary, a synonym is a database object that allows you to create an alias to another object. They are helpful for simplifying your queries and improving the way access to data and objects is handled.