Re: multiple schemas per user

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 18 Sep 2002 06:15:04 +1000
Message-ID: <3d878d4b@dnews.tpgi.com.au>

Well, I see you got correct replies from others... since USER=SCHEMA, the precise answer to your question is "no".

But I noticed this phrase in your original post:

"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?"

...at which point, I immediately thought "Workspace Manager", which is a feature of 9i. It lets you create virtual 'workspaces' within the database, to which you 'travel' in the same sort of way as you'd navigate a directory tree on your hard disk with the "cd" o/s command, and when you've arrived you can do transactionally-consistent manipulations of data without impacting on the "real" data in the "real" table. You can have as a many workspaces as you like: the only cost is that each workspace, and it's version of the table data, are stored as extra rows in the original table.

As an example:

select * from emp;
EMPNO ENAME SAL
-------- -------- -----
7394 SMITH 750

exec dbms_wm.enableversioning('SCOTT','EMP')
exec dbms_wm.createworkspace('HIGHSAL')
exec dbms_wm.gotoworkspace('HIGHSAL')

update emp set sal=sal+sal;
commit;
select * from emp;
EMPNO ENAME SAL
-------- -------- -----
7394 SMITH 1500 exec dbms_wm.gotoworkspace('LIVE') ("LIVE" is the workspace where 'real' data is visible)
select * from emp;
EMPNO ENAME SAL
-------- -------- -----
7394 SMITH 750 exec dbms_wm.mergeworkspace('HIGHSAL')
select * from emp;
EMPNO ENAME SAL
-------- -------- -----
7394 SMITH 1500 So now the model data in the "HIGHSAL' workspace is visible in the ordinary workspace that ordinary users see when they log on.

It's an incredibly powerful feature, very easy to use, and has rather more subtelty and flexibility than I've shown here. I could have killed for this feature about 8 years ago, and it's nice that it's there now.

It may be that this doesn't help your particular problem (and you didn't, in any case, mention what version of Oracle you are using), but it certainly addresses the general problem of how you allow a single user to have multiple simultaneous versions of the same table data.

Regards
HJR "roger" <rsr_at_rogerwrae.com> wrote in message news:TKIh9.129711$Jo.36522_at_rwcrnsc53...
> 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 - 15:15:04 CDT

Re: multiple schemas per user - possible?