Oracle synonyms (synonym) __oracle

1. Why Use synonyms:

Synonyms, as the name implies is two words, the same meaning, can replace each other. So what's its function?

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

B. Another purpose is to hide the information of some objects. For example, to find a table in another schema, you need to prefix the table name with the schema name. If you create a synonym, you can hide schema information. 2. How to create synonyms.

Syntax: CREATE [OR REPLACE] [public] synonym synonym_name for [schema.] Object

1. Create a private synonym:

For example: CREATE synonym Arwen for Table_of_arwen

This is the same using the SELECT * FROM Arwen or select * from Table_of_arwen.

Delete synonym: Drop synoym arwen.

Note: A private synonym can be used only by the user who created it. If other schemas are prefixed with the creator schema that must be added. This is the same as the other objects.

2. Create a common synonym:

CREATE public synonym Arwen for Table_of_arwen

All schemas can use public synonyms. For example, schema Scott accesses the schema Arwen table Table_of_arwen can use the SELECT * from Arwen

Some people may wonder why it is not necessary to prefix the schema. Maybe the system defaults to add it.

So the create public synonym Arwen the for Table_of_arwen or create public synonym the Arwen for Arwen.table_of_arwen is equivalent.

Other schemas can be found using Arwen.

Note: If the other schema does not have permission to find the table Table_of_arwen, it cannot use its synonyms to find it.

3. About error: The synonym conversion is no longer valid.

When a synonym is created, the object represented by the synonym is not checked for existence, and will be created successfully. For example create public synonym Arwen for no_table_exist

If there is not a table called No_table_exist or other database object called this.

The SELECT * from Arwen will be prompted with an error: the synonym conversion is no longer valid. Of course, if there was a deletion behind that watch, there would be the same mistake. 3. Aliases similar to synonyms

Oracle also has a concept called Alias. It is similar to the synonym function. It's only a small area of use. It is primarily used in an SQL statement and can only function temporarily. Like a temporary variable.

For example:

SELECT emp.ename,bonus.sal from EMP, bonus where emp.ename = Bonus.ename

We can give the EMP an alias. E,bonus take the B column also alias 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

Inside the keyword as is optional, if removed will also achieve the same function. You can use as in a column, but you cannot add an alias to a table.

Note:

1: Synonym: Create or replace synonym table name for large accounts. Name of the table; Remember to log in to the database with an authorized account, or failure to execute

2: After the synonym is created, you also need to authorize the user. such as: GRANT SELECT on table name to require an authorized account name;