Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

7.7 Workspaces

Oracle9i
introduced a new feature that relates to concurrency,
Workspace
Manager.

Workspace Manager allows current, proposed, and historical versions
of a row to exist together in the same table. It ensures that each
user sees the appropriate version of a row along with the rest of the
data in the database. For instance, a group may be working on a set
of changes to an overall compensation package, but not want proposed
changes to be seen until they have received final approval.

Workspace Manager uses workspaces to accomplish this. A workspace is
a virtual environment that one or more users share to make changes to
the data in the database. Users enter a workspace to make changes to
data. A workspace logically groups collections of new row versions in
one or more version-enabled tables, and isolates these versions until
they are explicitly merged with production data or until they are
discarded. A user in a workspace sees all of the data in the
database, as it existed when the workspace was created or last
refreshed, as well as the changes made from the workspace. Changes to
data made from within a workspace are only visible to users within
the workspace.

To use workspaces to version data in a table, you must first enable
the table for versioning. Workspace Manager can version-enable one or
more user tables in the database. The unit of versioning is a row.
When a table is version-enabled, all rows in the table can support
multiple versions of the data. Versioned rows are stored in the same
table as the original rows. The versioning infrastructure is not
visible to the users of the database, and application SQL statements
to select, insert, modify, and delete data continue to work in the
usual way with version-enabled tables. Workspace Manager
version-enables a table by renaming the table, adding a few columns
to the table to store versioning metadata, creating a view on the
version-enabled table using the original table name and defining
INSTEAD OF triggers on the view for SQL DML operations.

Savepoints are used to create new row versions, and allow users to
roll back changes to rows in version-enabled tables and see the state
of the database as it existed as of a particular milestone. Modifying
a row after a savepoint is created causes a new version of that row
to be created. Subsequent changes are applied to this new version in
the workspace until another savepoint is created. Users can compare
differences between any two savepoints.

A rollback operation deletes changes made in the workspace. A user
can delete either all changes made since the workspace was created or
only changes made after a savepoint. The user can limit the rollback
to one or more version-enabled tables.

A table can be version-enabled with the history option. If the
history option is specified, Workspace Manager adds a timestamp to
the row version every time it is changed. The history option works
with savepoints to provide a history of changes made to each row
version created by a savepoint. This allows users in a workspace to
go back to any point in time and view the entire database from the
perspective of changes made in that workspace. The history option
allows you to choose either to make a copy of the row version each
time a change is made to it or to overwrite values in the row version
with the most recent changes. If you keep all changes when
version-enabling a table, you keep a persistent history of all
changes made to all row versions in the table.

Workspaces can be organized into hierarchies, to implement cascading
groups of changes. Child workspaces can be merged and refreshed with
their parent workspace. Merging a workspace involves applying changes
made in a child workspace to its parent workspace. Refreshing a
workspace involves applying changes made in the parent workspace to a
child workspace. With Oracle Database 10g, you
can even allow a child workspace to have more than one parent
workspace. This feature allows a user in the child workspace to see
all of the changes made across all of the parent workspaces in one
place before they are merged into production. A user can work in more
than one workspace in a particular session. A user with workspace
privileges can freeze access to a workspace to allow no access to the
workspace, read-only access, or single-writer access.

You can choose to allow two or more workspaces to version the same
row at the same time. This allows multiple proposals to be developed
concurrently or to create multiple variations of a data scenario for
"what-if" analysis. When a row is
changed in both the child and parent workspace (usually through the
merge of another child workspace into the parent), a data conflict is
created. Conflicts can be checked and resolved at any time. Conflicts
are automatically detected when a merge or refresh operation is
requested. The list of conflicts is presented to the user in a
conflict view. Conflicts are resolved manually using a Workspace
Manager procedure. For each conflict, you can choose to keep the row
from the child workspace, the row from the parent workspace, or the
common base row's original data values. You must
resolve conflicts before you can perform a merge or refresh
operation.

Workspace Manager provides exclusive and shared version locks to
prevent conflicts between workspaces. These locks eliminate row
conflicts between a parent workspace and a child workspace. They can
also be used to reserve rows you will be updating in the future. You
can enable locking on a workspace, for a user session, on specified
rows, or a combination of the three.

Workspace Manager is tightly integrated with the Oracle database.
Oracle Database 10g Workspace Manager
enhancements include the ability to export and import version-enabled
tables, to use SQL*Loader to bulk load data into version-enabled
tables, to trigger events based on workspace operations, and to
define workspaces that are continually refreshed.