Re: A synonym question

On Jan 25, 11:54 am, nspa..._at_gmail.com 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.

jg

--
@home.com is bogus.
"If your attack is going too well, you're walking into an ambush." -
Infantry Journal