Using Synonyms for Location Transparency in Oracle

Over an application’s lifespan, its data very likely will move from one database to another or from one host to another. Therefore, it will simplify application maintenance if the exact physical location of a database object is shielded from the user (and the application).

The best way to implement such location transparency is through the use of synonyms. Instead of writing applications (or SQL*Plus reports) that contain queries that specify a table’s owner, such as

you should create a synonym for that table and then reference the synonym in the query, as shown here:

The logic required to find the data has thus been moved out of your application and into the database. Moving the table location logic to the database benefits you anytime you move the table from one schema to another.

In addition to hiding table ownership from an application, you can hide the data’s physical location through the use of database links and synonyms. By using local synonyms for remote tables, you move another layer of logic out of the application and into the database. For example, the local synonym BOOKSHELF, as defined in the following listing, refers to a table that is located in a different database on a different host. If that table ever moves, only the link has to be changed; the application code, which uses the synonym, will not change.

If the remote account used by the database link is not the owner of the object being referenced, you have two options. First, you can reference an available synonym in the remote database:

Here, BOOKSHELF, in the remote account used by the database link, is a synonym for another user’s BOOKSHELF table.

Second, you can include the remote owner’s name when creating the local synonym, as shown in the following listing:

These two examples result in the same functionality for your queries, but there are differences between them. The second example, which includes the owner’s name, is potentially more difficult to maintain because you are not using a synonym in the remote database and the remote object may be moved at a later time, thus invalidating your local synonym.