Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Introduction to Workspace Management - (Oracle 9i)

by Jeff Hunter, Sr. Database Administrator

Contents

1. Overview
2. OWM Concepts
3. Installing Oracle Workspace Manager
4. Creating and Managing Workspaces
5. The Workspace Administrator Role
6. Export/Import Considerations for Workspaces
7. Wrapping It Up

Overview

Any experienced DBA knows that the Oracle database management system, has some of the greatest support for concurrency and consistency across multiple transactions. In a typical transaction, Oracle will lock the row(s) being updated until either the transaction is committed or rolled back. For most applications, this is more than adequate, especially for databases with short-duration transactions or long-running transactions executed by a limited amount of users that are totally independent of the activity in other user sessions.

But consider a database where the DBA must support long-running transactions that could take hours or even days to complete. Oracle's standard transaction integrity techniques might prove insufficient. In these situations, all user sessions will have to ensure an unacceptable long wait for a lock on a row to be released by another user session. Furthermore, it is next to impossible to keep a domain of data that you are analyzing constantly, while other user sessions are performing DML operations that change this domain of data.

New with version 9i, Oracle introduces the idea of a "virtual workspace" called the Workspace Manager. Within this virtual workspace, the user session is able to have a domain of data that is constant, even though other user changes may be altering the data within this domain. Furthermore, the Workspace Manager enables the user session to return to this domain of data at a future point in time.

OWM Concepts

A workspace can be thought of as a virtual database facility that enables the user to maintain multiple versions of one row of data and it enables each workspace context to reference a different version of this row as compared to another workspace context. Basically, the workspace context enables the user to work with one version of the database in one workspace context, and with another version of the database in another workspace context, without compromising transactional integrity across the database.

It is possible for the user to perform data analysis with one version in one workspace context, while a large DML transaction runs in another context. The user has total availability to his data, even throughout the operation of the DML transaction. Once the transaction has committed, the user can merge the changed data with the original table. Workspace management enables users to manage one or more such workspaces.

The Workspace Manager enables users to version-enable a table, which enables them to maintain multiple versions of a row in that table. The table is the fundamental until for the Workspace Manager. When changes are made to a row in a regular table, the data is overwritten. When changes to a row are made on a version-enabled table, Oracle creates and maintains a new version of that row. Version-enabling a table can consume a large amount of storage space because the database has to maintain multiple versions of a row.

Installing Oracle Workspace Manager

Installing Workspace Manager is fairly straightforward. If the database was created with the Database Configuration Assistant (DBCA), then Workspace Manager will automatically be installed. When you manually create a database using your own scripts, you must install the Workspace Manager by connecting to the database as the SYS user and running the script: $ORACLE_HOME/rdbms/admin/owminst.plb.

It is worth mentioning what is involved when Workspace Manager is installed. The script first creates a user called WMSYS that will be used to hold all Workspace Manager objects (Packages, Views, etc.). The default password for WMSYS is WMSYS, but the account is locked when it gets created. (There is really no need to unlock and login to this account.). The WMSYS user will be assigned the following roles:

• CONNECT
• RESOURCE
• CREATE PUBLIC SYNONYM
• DROP PUBLIC SYNONYM
• CREATE ROLE

Creating and Managing Workspaces

Users should use the package DBMS_WS, (introduced in Oracle9i), to create and manage workspaces. The steps to effecting workspaces are as follows:

• Version-enable tables.
• Create workspaces.
• Grant privileges.
• Access the workspace.
• Resolve conflicts between the original rows and versioned rows.
• Refresh/merge workspaces.
• Disable versioning tables.
• Remove workspaces.

Version-Enable Tables

Throughout this short example, we will assume that we have a table called ORDERS with the following structure. (Make sure to make the ID column the primary key of the table):

  SQL> desc orders
   Name               Null?    Type
   ------------------ -------- ----------------------------
   ID                 NOT NULL NUMBER
   ORD_DATE           DATE
   AMOUNT             NUMBER

Use the ENABLEVERSIONING subprogram in the DBMS_WM package to version-enable this table.

  SQL> exec dbms_wm.enableversioning('orders');

  PL/SQL procedure successfully completed.

By default, the DBA role has been granted the WM_ADMIN_ROLE role to execute procedures in the DBMS_WM package. Optionally, you can provide a second parameter to specify the version of the table that you want to maintain. When you version-enable a table, Oracle changes the table name by adding a suffix, _LT. Oracle also creates additional tables, views, triggers, and indexes to track the different versions of rows. For example, Oracle creates a new table to maintain information about rows that are synchronized after it resolves conflicts. Oracle generates the name of this table by taking the original name of the version-enabled table and adding a suffix, _AUX. In our example, the name of the table will be ORDER_AUX.

The versioning of tables is subject to the following restrictions:

• The table must have a primary key.
• Only the owner of the table or a user with the WM_ADMIN_ROLE can version-enable that table. By default, the DBA role is granted the WM_ADMIN_ROLE role. The WM_ADMIN_ROLE role has all the Workspace Manager privileges.
• You cannot version-enable SYS tables.
• You cannot perform DDL operations on version-enabled tables.
• You cannot version-enable object tables and IOTs.
• You cannot version-enable tables that contain LONG columns.
• You cannot add referential integrity constraints on a version-enabled table.
• The table name of the table to be version-enabled cannot be more than 25 characters long.
• Column names cannot be more than 28 charaters long. (Thanks to Frank Hansen from DBA Solutions A/S for catching this restriction!)

Create Workspaces

Use the CREATEWORKSPACE procedure to create a workspace. Oracle creates this workspace in a tree hierarchy under the current workspace. By default, Oracle will create this workspace within the LIVE workspace.

You can identify the current workspace with the following query:

  SQL> SELECT dbms_wm.getworkspace FROM dual;

  GETWORKSPACE
  ------------
  LIVE

Any workspace you create will become a child workspace of this LIVE workspace. Now connect to the database has a user that has the WM_ADMIN_ROLE to run the following statement:

  SQL> connect sys/<password> AS sysdba

  SQL> exec dbms_wm.createworkspace('phase1_orders_ws');

The following considerations apply to the creation of a workspace:

• The name of the workspace is case-sensitive.
• You need the WM_ADMIN_ROLE role or you will need the CREATE_WORKSPACE or CREATE_ANY_WORKSPACE privilege.
• You do not automatically enter or access the workspace when you create the workspace.

Grant Privileges

The next step is to grant privileges to the users that will need to access this workspace. For example, you can grant users or roles the privilege to access the workspace, create a workspace, remove a workspace, merge workspaces, and rollback workspaces. The following example demonstrates how you can grant the access privilege on workspace phase1_orders_ws to user JHUNTER:

  SQL> exec dbms_wm.grantworkspacepriv('ACCESS_WORKSPACE', 'phase1_orders_ws', 'jhunter');

Looking at the above example, the first parameter specifies the privilege(s) being granted. You must separate multiple privileges with a comma. The second parameter specifies the workspace to grant privileges on. The third parameter specifies the user/role to whom you are granting these privileges.

Access the workspace

The default login is teh LIVE workspace. You can access any workspace by invoking the GOTOWORKSPACE procedure, as show below:

  SQL> exec dbms_wm.gotoworkspace('phase1_orders_ws');

You can perform queries and DMLs on the ORDERS table just as you would with a regular table. Workspace management is transparent to the user.

Modifications that you perform in this workspace WILL NOT be available to other user sessions until the workspace is merged with its parent. Oracle accomplishes this by maintaining different versions of the same row; you just have access to the versions that correspond to your workspace. Oracle still uses the default short-transaction-locking mechanism for the version of the row that you are updating.

Locks

Workspace Management enables you to apply additional locks on current and previous versions of the rows to eliminate conflicts between the parent and versioned records. For example, you can implement an exclusive lock to prevent users from modifying the previous versions of the rows, or you can implement a shared lock to allow users in the same workspace to share the current and previous versions of the rows. Use the SETLOCKINGON procedure from the DBMS_WM package to specify the session-level locking method, the SETWORKSPACELOCKINGMODEON to specify the row-level locking method, and the LOCKROWS method to specify access to parent and versioned rows.

Freeze a Workspace

You can freeze a workspace to disallow changes to rows in the version-enabled tables:

  SQL> exec dbms_wm.freezeworkspace('phase1_orders_ws', 'READ_ONLY');

You can reverse this process as follows:

  SQL> exec dbms_wm.unfreezeworkspace('phase1_orders_ws');

Other Workspace Operations

The DBMS_WM package also provides procedures for performing other operations. For example, the CREATESAVEPOINT procedure enables you to create savepoints in the current workspace, the ROLLBACKTOSP procedure enables you to roll back changes to a particular savepoint in the workspace, and ROLLBACKTABLE enables you to roll back changes on a single table.

The concept of savepoints in workspaces is very similar to that of normal transactional savepoints, but some differences exist. For example, although workspace-managed savepoints do not consume space, modifications to rows after the savepoint creation results in a new version of the row. Also, Oracle creates explicit savepoints to facilitate partial rollbacks in workspaces, and Oracle creates implicit savepoints when you create a new workspace.

Resolve Conflicts Between the Original Rows and the Versioned Rows

When one row is modified in two or more workspaces, you will have more than two versions besides the original row, and these rows are in conflict since you have to decide which change will prevail. You must resolve these conflicts before you can refresh workspaces (apply changes from the parent workspace to the child) or merge workspaces (apply changes made in the child workspace to its parent). Oracle will raise an error if you attempt to either merge or refresh different versions of data where conflicts exist.

  SQL> exec dbms_wm.refreshworkspace('phase1_orders_ws');
  BEGIN dbms_wm.refreshworkspace('phase1_orders_ws'); END;

  *
  ERROR at line 1:
  ORA-20056: conflicts detected for workspace: 'phase1_orders_ws' in table:
  'SCOTT.ORDERS'
  ORA-06512: at "SYS.LT", line 4836
  ORA-06512: at "SYS.LT", line 4823
  ORA-06512: at line 1


  SQL> SELECT * FROM orders_conf ORDER BY wm_workspace;

  WM_WORKSPACE                 ID ORD_DATE      AMOUNT WM_
  -------------------- ---------- --------- ---------- ---
  phase1_orders_ws            300 18-JUN-00      48000 NO
  BASE                        300                      NE
  LIVE                        300 18-JUN-00      48000 NO

As you can see from the above conflict, we need to query the ORDERS_CONF view to see the actual conflicts. We can see that there is a row in both the parent workspace (LIVE) and the child workspace (phase1_orders_ws) that conflict. Remember that the ID is the primary key of the table and needs to be unique.

The query results show the BASE (or original) values before the data was modified in any of the workspaces. The rows with the workspaces named LIVE and phase1_orders_ws display the data rows modified in these workspaces that are in conflict with each other. This query also displays the modified data.

You must resolve these conflicts manually with the RESOLVECONFLICTS procedure before you can either refresh the child version data from the parent version data or merge the child version of the data with the parent. Here is an example for resolving the above conflict.

  SQL> exec dbms_wm.beginresolve('phase1_orders_ws');
  SQL> exec dbms_wm.resolveconflicts('phase1_orders_ws', 'orders', 'id in (300)', 'PARENT');
  SQL> COMMIT;
  SQL> exec dbms_wm.commitresolve('phase1_orders_ws');

The first line prepares the environment for conflict resolution, the second command performs conflict resolution, the third command commits pending short-term transactions, and the fourth statement commits the changes as a result of the resolved conflicts.

Now that the conflicts have been resolved, the following query confirms this fact:

  SQL> SELECT * FROM orders_conf ORDER BY wm_workspace;

  no rows selected

Refresh/Merge Workspace

Now that you have resolved data conflicts, you can either merge or refresh the different versions of data. You are refreshing the workspace when you copy the parent workspace to the child workspace. You must resolve conflicts prior to refreshing or merging.

You can invoke the REFRESHWORKSPACE procedure to refresh all the data and REFRESHTABLE to refresh data for a single table. You cannot refresh the LIVE workspace; it is the topmost workspace in the hierarchy and does not have a parent workspace to refresh from.

  SQL> exec dbms_wm.refreshworkspace('phase1_orders_ws');

Merging is done when you apply modifications from the child workspace to the parent workspace. Again, you must resolve conflicts before merging. You can invoke MERGEWORKSPACE to merge all data or invoke MERGETABLE to merge just one table:

  SQL> exec dbms_wm.mergeworkspace('phase1_orders_ws');

The previous command merges the changes in the phase1_orders_ws workspace to its parent workspace, which is the LIVE workspace in our example. The MERGEWORKSPACE procedure has two optional parameters: one to specify whether a savepoint has to be created in the parent workspace before merging and one to specify if the workspace should be removed after the merge. Because we did not specify the optional parameters in our example, the database does not create any savepoint in the LIVE workspace before the merge, and it does not remove the workspace after the merge.

Disable Versioning Tables

Once you have completed your versioned operations; you must disable the versioning of the tables to minimize the possibility of creating unnecessary rows. This action also gets rid of the overhead of maintaining additional indexes, views, and triggers. You invoke the DISABLEVERSIONING procedure to perform this action, as shown below:

  SQL> exec dbms_wm.disableversioning('orders');

You either must be the owner of the table or a user with the WM_ADMIN_ROLE role to perform this task. This command will fail if you have modified data in the table. If you want to execute this command regardless, you must specify that you want to FORCE it by providing the second argument, which is an option agrument. If the FORCE parameter is TRUE, it will force changes to be discarded in the child workspace table before disabling versioning for the table. This action does not remove either the workspace hierarchy or the savepoints you established.

  SQL> exec dbms_wm.disableversioning('orders', true);

Remove Workspace

The final step is to remove the workspace using the REMOVEWORKSPACE procedure, as shown below:

  SQL> exec dbms_wm.removeworkspace('phase1_orders_ws');

This code rolls back the data in the workspace and deletes the workspace definition from the database. You must invoke teh REMOVEWORKSPACETREE procedure to remove all the workspaces in a hierarchy.

The Workspace Administrator Role

Privileges in the Workspace Manager are distinct from the regular privileges in Oracle. You have the following workspace-level privileges:

• ACCESS_WORKSPACE
• CREATE_WORKSPACE
• REMOVE_WORKSPACE
• MERGE_WORKSPACE
• ROLLBACK_WORKSPACE

You also have the following system-level privileges:

• ACCESS_ANY_WORKSPACE
• CREATE_ANY_WORKSPACE
• REMOVE_ANY_WORKSPACE
• MERGE_ANY_WORKSPACE
• ROLLBACK_ANY_WORKSPACE

The Workspace Manager provides the WM_ADMIN_ROLE role that contains all the workspace-level and system-level privileges that you can grant to the user. Oracle grants the WM_ADMIN_ROLE role to the DBA role.

You can assign workspace-level privileges and system-level privileges to a user with the GRANTWORKSPACEPRIV and the GRANTSYSTEMPRIV procedures, respectively. You use the REVOKEWORKSPACEPRIV and the REVOKESYSTEMPRIV procedures to revoke privileges.

Export/Import Considerations for Workspaces

You can perform exports and imports on version-enabled databases much like the same as you would with regular databases; however, you must take the following restrictions into consideration:

• The target database for the import must have Workspace Manager installed, it should only contain the LIVE workspace, and it should not contain and version-enabled tables.
• You can only export or import the entire database in the case of version-enabled databases. You cannot perform export or imports on just the schema, tables, partitions, or workspaces.
• You must set the IGNORE parameter to Y for imports.
• You cannot perform an ownership change of objects using FROMUSER and TOUSER parameters.

Wrapping It Up

• Workspace Management enables different users to modify a table and still use their own version of the data until changes are merged with the original table. (Or rolled back).
• The DBMS_WM package contains procedures to enable workspace management.
• The WM_ADMIN_ROLE role contains all privileges to perform any workspace management tasks.
• You can either merge changes to the table with the parent, or you can discard these changes.