Re: multiple schemas per user - possible?

Hi Jaap,

Yes it can.

You basically:

1. Make a table "version enabled". This does a whole heap of things but basically it adds a number of columns to the table, renames the table, creates a view the same name as the original table, creates an instead of trigger on the view and creates a number of new indexes and table structures.
2. Create a "workspace". This is basically your view of the table. Changes made when in a workspace are only visible within the workspace. All changes are recorded in the same renamed table, but the new columns contain information on which workspace made which change and the view and instead of triggers ensure changes made by other workspaces are not visible. Note you can have multiple versions of the *same row*, each made by different workspaces (and you can also keep a history of all such changes). Note also that the default workspace is LIVE, or the base view of the table.

So you need two things to make this work. You need a version enabled table and you need multiple workspaces (all set-up via the dbms.wm package)

3) Changes can be "Merged" back to the parent workspace. Or changes in the parent workspace can be "Refreshed" back. As it's possible to have multiple versions of the same logical row, conflicts are possible and may need to be resolved before a merge or refresh. There's a nice mechanism that allows you to do this.

The beauty with all this is that all these changes are fundamentally recorded in the same table. This table can of course be backed up and protected like any other table.

In your example, an option could be to provide everyone with their own workspace. They can all make changes and perform tests and what if analysis without such changes being made visible to each other (until such time as you may wish to).

Worth investigating.

Cheers

Richard

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:0idgou8ueqdq2a1jjrmpeoppvknmo25p27_at_4ax.com...
> On Wed, 18 Sep 2002 06:15:04 +1000, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
>
> ...
> >...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.
> ...
>
> In the old days, when I used to work on a UNISYS system with a
> DBMS1100 database, there was this feature called 'changefiles', used
> for development and testing. The database contained a basic set of
> data, on which the software you were testing performed DML. Changed
> pages where not stored in the database however, but in your personal
> changefile. When accessing a page the system first checked if the page
> was in your changefile and if it was it would use that version,
> otherwise it would retrieve the page from the live database.
> Everybody of the development and testteam could use the same database
> in this way without 'seeing' each other's changes.
>
> Could this Workspace Manager be used in the same way?
>
> Jaap.
Received on Wed Sep 18 2002 - 03:51:57 CDT