Oracle Synonyms Go

1. Why use synonyms:

Synonyms, as the name implies, are the same as the meaning of two words, can be replaced with each other. What is its role?

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

B. Another goal is to hide information about 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 the schema information.

2. How to create synonyms.

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

1. Create a private synonym:

Example: CREATE synonym Arwen for Table_of_arwen

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

Delete synonym: Drop synoym arwen.

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

2. Create a public synonym:

CREATE public synonym Arwen for Table_of_arwen

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

Some people may wonder why this situation does not have to be prefixed to the schema. The system may be added by default.

So create public synonym Arwen for table_of_arwen or create public synonym 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 look up the table Table_of_arwen then it cannot be found using its synonyms.

3. About error: Synonym conversion is no longer valid.

When a synonym is created, it is not checked for the existence of the object represented by the synonym 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 has an error message: The synonym conversion is no longer valid. Of course, if there is a table after the deletion of the same error.

3. Aliases similar to synonyms

Oracle also has a concept called aliases. It is similar to the synonym function. It is only used in a small way. Used primarily in an SQL statement, only 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 and alias the query statement 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, and the same functionality can be achieved if it is removed. As is available in the column, but aliases to the table cannot be added.

Oracle Synonyms Go