Synonym (Synonym)

Source: Internet
Author: User
1. Why use Synonyms:

Synonyms, as the name implies, mean that two words share the same meaning and can be replaced with each other. What are their functions:

A's most obvious goal is to simplify SQL. If the name of a database object is too long, you can create a shorter synonym, which is equivalent to the abbreviation.

B. another purpose is to hide the information of some objects. for example, to search for a table in another schema, you must add the schema name before the table name as the prefix. if you create a synonym, you can hide schema information.

2. How to Create a synonym.

Syntax: Create [or replace] [public] synonym synonym_name for [schema.] Object

1. Create a private synonym:

Example: Create synonym Arwen for table_of_arwen

In this way, select * From Arwen or select * From table_of_arwen is the same.

Delete synonym: Drop synoym Arwen.

Note: A private synonym can only be used by users who create it. If other schemas are used, the Creator Schema must be prefixed. This is the same as other objects.

2. Create a public synonym:

Create public synonym Arwen for table_of_arwen

All schemas can use public synonyms. For example, schema Scott can access the table_of_arwen table of Schema Arwen using select * From Arwen.

Some may wonder why the schema prefix is not used in this case. Maybe the system adds the prefix by default.

Therefore, create public synonym Arwen for table_of_arwen or create public synonym Arwen for Arwen. table_of_arwen is equivalent.

Other schemas can be searched using Arwen.

NOTE: If other schemas do not have the permission to search for the table table_of_arwen, the table cannot be searched using its synonyms.

3. Error: synonym conversion is no longer valid.

When a synonym is created, the system does not check whether the object represented by the synonym exists. For example, create public synonym Arwen for no_table_exist

Suppose there is no table named no_table_exist or another database object named this table.

When select * From Arwen is selected, the following error occurs: synonym conversion is no longer valid. Of course, if the table is deleted, the same error occurs.

3. Alias similar to synonym

There is also an alias in Oracle. It is similar to the synonym function. It is only used in a small scope. It is mainly used in an SQL statement and can only work temporarily. It is like a temporary variable.

For example:

Select EMP. ename, bonus. Sal from EMP, bonus where EMP. ename = bonus. ename

We can give EMP an alias E and bonus an alias for Column B. The query statement is as follows:

Select E. ename as myname, B. Sal as mysal from emp e, bonus B where E. ename = B. ename

The keyword "as" is optional. If it is removed, the same function will be implemented. As is available in the column, but aliases for the table cannot be added.

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.