Oracle Database SYNONYM (SYNONYM) Usage

Source: Internet
Author: User

Oracle Database SYNONYM (SYNONYM) Usage

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


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


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


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


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

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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.