Oracle Database SYNONYM (SYNONYM) Usage

Oracle Database SYNONYM (SYNONYM) Usage

This tutorial uses examples to illustrate how to create and delete Synonyms (SYNONYM) in Oracle Data ).

Concept

Synonyms refer to database objects (tables, views, sequences, stored procedures, and other database objects) referenced by another name. For example:

Creating a synonym can exclude the restriction of an object name.
If your database has multiple users, USER_A can only use USER_ B .TABLE1 to access TABLE1 of USER_ B.
Create a synonym abc pointing to USER_ B .TABLE1, then you can select * from abc, and the public synonym will appear directly in front of all users, so it is much easier to develop it.

Create Synonym

Syntax:

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

Syntax description:

Or replace: allows you to create a new one without using DROP (if a synonym already exists ).

PUBLIC: The created synonym is a global synonym, which can be used by all database users.

Schema: the schema of the object to which the synonym is to be created. If it is omitted, the default application object is under the current schema.

Object_name: the object to create a synonym. It can be of the following types:

Tableviewsequencestored procedurefunctionpackagematerialized viewjava class schema OBJECTUSER-DEFINED OBJECTSYNONYM

Example:

CREATE PUBLIC SYNONYM suppliers FOR app.suppliers;

The name of the synonym created in the example is suppliers. In this way, users in other schemas can use this synonym to use the suppliers table in the app without adding an app. For example:

SELECT *FROM suppliers;

Delete Synonym

Syntax

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

Note:

PUBLIC: Allow deletion of PUBLIC synonyms. If the PUBLIC keyword is used, the schema can be omitted.

FORCE: used to FORCE Delete synonyms, even if it has other dependencies in the database.

Example:

DROP PUBLIC SYNONYM suppliers;