Synonyms

What is Synonym?
A synonym is an alias for an Oracle object. The synonym is not an actual Oracle object. This object can be a table, view, sequence, package, function, procedure. The only restriction regarding the object is that objects, so that users it cannot be contained inside a procedure. Synonyms are created to point to these need to know only the synonym's name.

Why use Synonym?

Synonyms provide many benefits, including the following:

Requiring no storage other than their definition on the data dictionary

Simplifying SQL coding Improving database security

Providing public access to an Oracle object Synonyms are used to improve database security by masking the underlying object's actual name, owner, and location. This holds true for distributed database where an object can be referred to on a remote database.

How to use Synonym?

Use the SQL command create synonym to create a synonym. The following example creates public synonym named emp for the table EMPLY in the schema emp_01 CREATE PUBLIC SYNONYM EMP FOR EMP OLEMPLY, A synonym can be used to stand for its underlying object in any of the following Data manipulation Language Statements.

1. Select
2. Insert
3. Update
4. Delete

Synonyms can also be used in the following Data Definition Language Statements: 1. Grant
2. Comment
3. Revoke
4. Audit
5. Noaudit

The following code examples illustrate the use of synonyms in SQL code.

Not using synonyms in the SQL statement;
SELECT "FROM EMP OLEMPLY,
Using synonyms in the SQL statement;
3 Dropping a Synonym

To remove a synonym from the database and its data dictionary, use the drop synonym command. You use public keyword to drop a public synonym. The following synonym drops the private synonym emp:

DROP SYNONYM EMP;

Renaming a Synonym

You can rename a private synonym of a table by using the rename command. The following example renames the dept synonym to the bus unit synonym:

RENAME DEPT TO BUS UNIT;

All grants and indexes belonging to the old synonym are transferred to the new system.

Domain Name Search