Re: A synonym question

From: joel garry <>
Date: Fri, 25 Jan 2008 16:36:33 -0800 (PST)
Message-ID: <>

On Jan 25, 11:54 am, wrote:
> I have the following scenario:
> - Tables and views are in Schema1 and Schema2
> - Synonyms are created private under UserX
> this works fine no problem.
> Lately I created Schema3 and Schema4 to backup 1 and 2 respectively.
> Now I want to access these new schemas logging with userX and using
> the same synonyms, but the synonyms point to schema 1 and 2 how can I
> switch the same synonyms to point to schema3 and 4 dynamically?
> Thank You,
> Ziko

Note the existence of tables like all_synonyms. You can write scripts to generate the synonym drop and create.

In the past I've found this very confusing, I wound up doing things like including a schema descriptor in the synonym name, because I would generally want to join or compare them somehow rather than switch between them. YMMV.

Making it not-too-easy to switch between test and production data has saved my rear, too.


-- is bogus.
"If your attack is going too well, you're walking into an ambush." -
Infantry Journal
Received on Fri Jan 25 2008 - 18:36:33 CST

Original text of this message