table structure behind oracle workspace manager

The user can jump back and forth between the scenarios and the “live” workspace (which is the default). They can also run an API command ( dbms_wm.SetDiffVersions) to generate a differences report which shows all the inserts, updates, and deletes that have occurred since a savepoint.Īn example of using workspaces is where a user could create one or two workspaces, each representing a different scenario.

One example of using savepoints is that a user could create a savepoint, make changes, go back and view the database as of the savepoint, and can rollback all changes to a savepoint. Note: the default workspace for a user session is called “ LIVE“, and the default savepoint is “ LATEST“. I haven’t tried this myself but it looks like a powerful feature. The Valid Time use case allows your users to set a date/time range on each record multiple versions of a unique row can be created with non-overlapping date ranges updates can be done within the context of a given date/time range, which will cause rows that span the boundary of the range to be split into multiple versions. The Row History use case is similar to using Flashback Query which is a more modern feature of the database however, since it can be enabled or disabled individually for each table, it may require less storage space to support querying back as far as the user would like in time.

Valid Time – support date/time range validity for each record.

Row History – store a history of every change for every record.

Workspaces – a private area for modifications that are not visible to normal users.

Savepoints – a snapshot of a point in time.

OWM allows the developer to take a leap over the complexities involved in a number of use cases, such as: As described by Tim Hall, it allows multiple transactionally consistent environments to exist within one database.ĭon’t confuse OWM with Oracle APEX workspaces, or with Analytic Workspace Manager for Oracle OLAP. Oracle Workspace Manager (OWM) is a feature of the Oracle database that was built back in the 8i days and installed in Oracle by default since 9i. Instead, I’m adding this preamble to say “this was a very interesting feature of Oracle I learned but didn’t quite get to use, but hopefully someone will find it useful”. This means I’m unable to add a triumphant postscript saying “this was a great success and it’s being used to this day”. However, for unrelated reasons the project was significantly delayed and later rebooted with an updated set of requirements, which no longer require Oracle Workspace Manager.

This is an article I wrote quite some time ago and put “on ice” until I completed the client project it was related to.

Table structure behind oracle workspace manager