A synonym question

#11

On Jan 25, 2:28*pm, nspa... (AT) gmail (DOT) com wrote:

Quote:

On Jan 25, 3:14*pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com
wrote:

nspa... (AT) gmail (DOT) 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

Not.

Accounts and schema's have a relationship. Your userX has
schemaX; schemaX has synonyms pointing to One.table_1
and One.view_1 (without going into the discussion whether
one stands for the account, or for the schema).
One belongs to user1 (which is schema1). You cannot
point to user4 'dynamically'.

Just drop the synonym, and re-create it while pointing to
the other schema.

The other schema's being backups, I fail to see the point
of this exercise. Do not touch your backup!

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up- Hide quoted text -

- Show quoted text -

The new created schemas are actually an archive not backup my
appologies.
I want to connect with the same user and dynamically switch between
main database and archive one.

Thank You,
Ziko- Hide quoted text -

- Show quoted text -

Then you'll need to create additional synonyms pointing to your
archive schema objects or drop and recreate these synonyms so they
point to the desired objects; there exists no dynamic reconfiguration
of synonyms in Oracle.

David Fitzjarrell

#12

On Jan 25, 2:28*pm, nspa... (AT) gmail (DOT) com wrote:

Quote:

On Jan 25, 3:14*pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com
wrote:

nspa... (AT) gmail (DOT) 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

Not.

Accounts and schema's have a relationship. Your userX has
schemaX; schemaX has synonyms pointing to One.table_1
and One.view_1 (without going into the discussion whether
one stands for the account, or for the schema).
One belongs to user1 (which is schema1). You cannot
point to user4 'dynamically'.

Just drop the synonym, and re-create it while pointing to
the other schema.

The other schema's being backups, I fail to see the point
of this exercise. Do not touch your backup!

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up- Hide quoted text -

- Show quoted text -

The new created schemas are actually an archive not backup my
appologies.
I want to connect with the same user and dynamically switch between
main database and archive one.

Thank You,
Ziko- Hide quoted text -

- Show quoted text -

Then you'll need to create additional synonyms pointing to your
archive schema objects or drop and recreate these synonyms so they
point to the desired objects; there exists no dynamic reconfiguration
of synonyms in Oracle.

David Fitzjarrell

#13

On Jan 25, 2:28*pm, nspa... (AT) gmail (DOT) com wrote:

Quote:

On Jan 25, 3:14*pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com
wrote:

nspa... (AT) gmail (DOT) 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

Not.

Accounts and schema's have a relationship. Your userX has
schemaX; schemaX has synonyms pointing to One.table_1
and One.view_1 (without going into the discussion whether
one stands for the account, or for the schema).
One belongs to user1 (which is schema1). You cannot
point to user4 'dynamically'.

Just drop the synonym, and re-create it while pointing to
the other schema.

The other schema's being backups, I fail to see the point
of this exercise. Do not touch your backup!

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up- Hide quoted text -

- Show quoted text -

The new created schemas are actually an archive not backup my
appologies.
I want to connect with the same user and dynamically switch between
main database and archive one.

Thank You,
Ziko- Hide quoted text -

- Show quoted text -

Then you'll need to create additional synonyms pointing to your
archive schema objects or drop and recreate these synonyms so they
point to the desired objects; there exists no dynamic reconfiguration
of synonyms in Oracle.

David Fitzjarrell

#14

On Jan 25, 11:54*am, nspa... (AT) gmail (DOT) com wrote:

Quote:

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

#15

On Jan 25, 11:54*am, nspa... (AT) gmail (DOT) com wrote:

Quote:

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

#16

On Jan 25, 11:54*am, nspa... (AT) gmail (DOT) com wrote:

Quote:

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

#17

On Jan 25, 11:54*am, nspa... (AT) gmail (DOT) com wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

A synonym question

comp.databases.oracle.misc comp.databases.oracle.misc

Re: A synonym question - 01-25-2008 , 03:00 PM

Re: A synonym question - 01-25-2008 , 03:00 PM

Re: A synonym question - 01-25-2008 , 03:00 PM

Re: A synonym question - 01-25-2008 , 06:36 PM

Re: A synonym question - 01-25-2008 , 06:36 PM

Re: A synonym question - 01-25-2008 , 06:36 PM

Re: A synonym question - 01-25-2008 , 06:36 PM