About Oracle Workspace Manager

Oracle9i introduced the concept of a database workspace manager. A workspace is an environment for a long-term transaction that allows versioning of table rows. A workspace can be shared among multiple users. The concept of workspace manager involves a series of short transactions and multiple data versions to implement a complete long-transaction event that maintains atomicity and concurrency.

The Workspace Manager (WKS) is installed by default in all seed and DBCA databases.

workspace_status.sql

COLUMN WORKSPACE        FORMAT a10 HEADING 'Workspace'
COLUMN owner            FORMAT a10 HEADING 'Owner'
COLUMN freeze_status    FORMAT a8 HEADING 'Freeze|Status'
COLUMN resolve_status   FORMAT a8 HEADING 'Resolve|Status'
COLUMN parent_workspace FORMAT a10 HEADING 'Parent|Workspace'
COLUMN freeze_mode      FORMAT a8 HEADING 'Freeze|Mode'

start title80 'Workspace Status'
spool rep_out\&db\workspace_status

select
  workspace,
  NVL(parent_workspace,'NONE') parent_workspace,
  owner,
  freeze_status,
  NVL(freeze_mode,'NONE') freeze_mode,
  resolve_status
from
  Remote_DBA_workspaces
/
spool off
ttitle off

Example output from workspace status report.

Date: 10/14/01                                              Page:   1
Time: 06:25 PM                Workspace Status              SYS
                              galinux1 database 

           Parent                Freeze   Freeze   Resolve
Workspace  Workspace  Owner      Status   Mode     Status
---------- ---------- ---------- -------- -------- --------
LIVE       NONE       SYS        UNFROZEN NONE     INACTIVE

Oracle Workspace manager workspaces are monitored using the Oracle Workspace Manager (dbms_wm) package with workspaces also provides a Workspace Manager interface accessible from the database listing of OEM.

Features of Oracle Workspace Manager

Oracle lists the following features of Oracle workspace manager:

• Enables and disables versioning on one or more user tables as needed. The unit of versioning is a row.
• Captures changes to rows as new row versions and stores them in the version-enabled table along side the original rows.
• Provides metadata information views to DBAs and developers to manage and report on all aspects of the Workspace Manager environment.
• Creates workspaces, shared by one or more users and organized in hierarchies, to group and isolate collections of row versions from one or more version-enabled tables.
• Tracks versions created in each workspace and automatically shows users the appropriate versions in the context of the entire database.
• Creates savepoints to group collections of versions within a workspace. Changes can be rolled back to a savepoint and users can go to a savepoint to see the state of the database as it existed as of a particular milestone.
• Automatically detects data conflicts between workspaces and enables users to choose which version to keep: parent, child or original.
• Allows changes made in a workspace to be completely or partially merged (into parent), refreshed (from parent) or rolled back (deleted).
• Controls access to workspaces, workspace operations and data through access modes, privileges and locks respectively.
• Compresses workspaces to reduce storage required and enhance performance by deleting savepoints and intermediate versions.
• Optionally, enables persistent history on a version-enabled table to timestamp all or just the latest change made to all row versions in that table. This enables users to "goto date" to see the state of the database as it existed at that time and see who made the changes.
• Integrated with Oracle9i Database including support for DDL on version-enabled tables, triggers, constraints, replication and import/export
  

Using Oracle Workspace Manager for Auditing

Oracle workspace manage can be used like Oracle LogMiner to audit changed rows within tables.  Justin Cave published this example of using Oracle Workspace Manager for auditing:

SQL> create table my_new_table (
  2    col1 number,
  3    col2 number,
  4    constraint pk_new_tbl primary key( col1 )
  5  );
 
Table created.
 
SQL> exec dbms_wm.enableVersioning( 'MY_NEW_TABLE', 'VIEW_WO_OVERWRITE' );
 
PL/SQL procedure successfully completed.
 
SQL> insert into my_new_table values( 1, 10 );
 
1 row created.
 
SQL> insert into my_new_table values( 2, 20 );
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> update my_new_table
  2     set col2 = col2 + 1;
 
2 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from my_new_table;
 
      COL1       COL2
---------- ----------
         1         11
         2         21
 
SQL> column col1 format 9999
SQL> column col2 format 9999
SQL> column wm_username format a10;
SQL> column wm_username format a15;
SQL> column wm_createtime format a25;
SQL> column wm_retiretime format a25;

SQL> select col1, col2, wm_optype, wm_username, wm_createtime, wm_retiretime
  2    from my_new_table_hist;
 
 COL1  COL2 W WM_USERNAM WM_CREATETIME             WM_RETIRETIME
----- ----- - ---------- ------------------------- -------------------------
    1    10 I SCOTT      11-JUL-09 10.50.13.859000 11-JUL-09 10.50.37.062000
                          AM -04:00                 AM -04:00
 
    2    20 I SCOTT      11-JUL-09 10.50.24.171000 11-JUL-09 10.50.37.109000
                          AM -04:00                 AM -04:00
 
    1    11 U SCOTT      11-JUL-09 10.50.37.062000
                          AM -04:00
 
    2    21 U SCOTT      11-JUL-09 10.50.37.109000
                          AM -04:00