When should database synonyms be used?

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.

In this way, you can update/delete the data in the mock table without affecting your source table. When you are ready to use the source table, all you need to do is re-direct the synonym.

Check out the Oracle documentation on synonyms.

In addition to the other answers here, they are also commonly used for:

• Providing easy to use names for remote tables, i.e. over database links
• Tables that you need to be accessible to all users, i.e. public synonyms

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.