• In a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.

    The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.