The synonyms in oracle database

[Norse]

I am SQL beginner and want to know about the existence of synonyms in database and their usage. I am aware about only one feature, it provides the second and short name for a database object that has a large name.Can you provide me some more information and possible features of synonyms.Please help me !!!!

[Praetor]

Synonyms :

The synonyms provide an alternative name for any database object (Table,view,sequence, procedure, stored function, package) and much more.
It allows applications to execute without any updation without any headache of which user owns the table or view and which database holds particular object.

You can use synonyms with following DML statements : SELECT, INSERT, UPDATE, DELETE, EXPLAIN PLAN and LOCK TABLE
and with DML statements like AUDIT, NOAUDIT, GRANT, REVOKE and COMMENT.

[Katty]

There are two ways of creating synonyms for different purpose that we would discuss here.
1. Simple synonyms
2. Public synonyms

Creating Simple SYNONYMS:

Assume, The table Library_booksheld by SCOTT schema and you want to create a synonyms named "L_books". To do this you need to use this statements :

SQL> CREATE SYNONYM L_books
                FOR SCOTT.Library_books;

[kelfro]

Creating PUBLIC Synonyms :

You can create a PUBLIC synonyms on any object of any database that is on remote location.Here I will show you a statement which creates a synonyms named "Emp" for database object "EMPLOYEES" that is on remote location and the name of database is "Org_database". To create the synonyms for EMPLOYEES table in Kelfro schema use below statement

SQL> CREATE PUBLIC SYNONYM emp 
         FOR Kelfro.employees@Org_database;

[Zecho]

Dropping Synonyms :

You can drop the created synonyms of any simple or Public synonyms using this drop statement for synonyms.Before dropping any synonyms you should have privilege to drop.

SQL> DROP PUBLIC SYNONYM emp;