Using Synonyms in Oracle

This article is written about how to create and drop synonyms in Oracle with syntax and examples.

Description

A synonym is an alternative identify for objects such as tables, views, sequences, saved procedures, and other database objects.

You normally use synonyms when you are granting access to an object from another schema and you do not prefer the users to have to worry about understanding which schema owns the object.

Create Synonym (or Replace)

You might also desire to create a synonym so that customers do not have to prefix the desk title with the schema identify when the use of the desk in a query.

Syntax

The syntax to create a synonym in Oracle is:

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

OR REPLACE Allows you to recreate the synonym (if it already exists) besides having to issue a DROP synonym command. PUBLIC It ability that the synonym is a public synonym and is on hand to all users. Remember although that the person must first have the excellent privileges to the object to use the synonym. schema The terrific schema. If this phrase is omitted, Oracle assumes that you are referring to your very own schema. object_name The identify of the object for which you are growing the synonym. It can be one of the following: table view sequence stored procedure function package materialized view java type schema object user-defined object synonym

Example

Let’s appear at an instance of how to create a synonym in Oracle.

For example:

CREATE PUBLIC SYNONYM suppliers
FOR app.suppliers;

This first CREATE SYNONYM instance demonstrates how to create a synonym known as suppliers. Now, users of other schemas can reference the desk called suppliers barring having to prefix the desk name with the schema named app. For example:

SELECT *
FROM suppliers;

If this synonym already existed and you wanted to redefine it, you may want to usually use the OR REPLACE phrase as follows:

CREATE OR REPLACE PUBLIC SYNONYM suppliers
FOR app.suppliers;

Drop synonym

Once a synonym has been created in Oracle, you might at some factor want to drop the synonym.

Syntax

The syntax to drop a synonym in Oracle is:

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

PUBLIC Allows you to drop a public synonym. If you have particular PUBLIC, then you don’t specify a schema. force It will force Oracle to drop the synonym even if it has dependencies. It is probable not a good thinking to use pressure as it can cause invalidation of Oracle objects.

Example

Let’s seem to be at an instance of how to drop a synonym in Oracle.

For example:

DROP PUBLIC SYNONYM suppliers;

This DROP declaration would drop the synonym called suppliers that we described earlier.