A synonym question

#1

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

#2

nsparta (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

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

#3

nsparta (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

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

#4

nsparta (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

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

#5

nsparta (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

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

#6

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

Quote:

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

#7

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

Quote:

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

#8

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

Quote:

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

#9

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

Quote:

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

#10

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