multiple schemas per user - possible?

Hello there.

Is there any way to create/manage/user multiple "schemas" for a single database user?

As I understand things, each user having a private schema, has a set of tables, and these tables are all distinct from any other identically named tables that reside in the private schema of any other users.
That is fine great and handy.

But, how do we account for a situation where a single user would like to have serveral distinct versions of the tables in his schema? For example, when developing/testing new versions of some software, or simply maintaining parallel data sets for the same versions of the tables...

I've read about synonyms, and while I understand that it is possible to achieve this sort of indirection with them, I sure hope there is a better answer than that available!

To me, it seems like a user should be able to create multiple "schemas", add objects to the various schemas, and specify a default schema which would be used in the absence of an explicit schema reference, etc...

Indeed, this seems like such a fundamental concept to me that the fact that Oracle doesn't seem to work that way tells me I must be missing something.

Any suggestions appreciated. Received on Tue Sep 17 2002 - 11:28:35 CDT