SYNONYM USED IN ORACLE 11G

SYNONYM


CREATE SYNONYM TEST_GL_OPEN_PERIODS_STG FOR TEST.TEST_GL_OPEN_PERIODS_STG;
CREATE SYNONYM TEST_GL_CLOSE_PERIODS_STG FOR TEST.TEST_GL_CLOSE_PERIODS_STG;
CREATE SYNONYM TEST_GL_USERS_TEMP_STG FOR TEST.TEST_GL_USERS_TEMP_STG;
CREATE SYNONYM TEST_GL_CONTROLS_STG FOR TEST.TEST_GL_CONTROLS_STG;
CREATE SYNONYM TEST_GL_ACTIVITY_DOWNLOAD FOR APPS.TEST_GL_ACTIVITY_DOWNLOAD;


It is excellent for staging mock tables when testing.
For example, if your source tables contain millions of records and you want to test a small subset of data,
you can use synonyms to re-direct the source table to a smaller table you control
so you can stage various scenarios.

I usually see synonyms used when the DBA wishes to separate database objects into different schemas,
but wants/needs some of these objects to be visible to other schemas (but doesn't want to give direct
access to them).

An example I've seen most recently: Several web apps run by the same company. Users usually have
access to more than one of these apps, and the will only have one user account to access these apps.
User-account information is stored in a USER_ACCOUNTS schema, and all other apps are in their own schemas
and access the USER_ACCOUNTS schema via synonyms.



Synonym is an alias for any Table, View, Snap-shot, Sequence, Procedure, Function or Package.
Because synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Advantages of having synonyms -
Synonyms are often used for security and convienience.
for Example, they can do the following things.

    Mask the name and owner of an object.
    Provide location transparency for remote objects of a distributed database.
    Simplify SQL statements for database users.