Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Workspace Manager tips

Oracle Database Tips by Burleson Consulting

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.


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

  NVL(parent_workspace,'NONE') parent_workspace,
  NVL(freeze_mode,'NONE') freeze_mode,
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
---------- ---------- ---------- -------- -------- --------

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;
----- ----- - ---------- ------------------------- -------------------------
    1    10 I SCOTT      11-JUL-09 11-JUL-09
                          AM -04:00                 AM -04:00
    2    20 I SCOTT      11-JUL-09 11-JUL-09
                          AM -04:00                 AM -04:00
    1    11 U SCOTT      11-JUL-09
                          AM -04:00
    2    21 U SCOTT      11-JUL-09
                          AM -04:00




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.