1 Introduction to Workspace Manager

1.1.1 Workspace Hierarchy

There can be a hierarchy of workspaces in the database. For example, a workspace can be a parent to one or more workspaces (child workspaces). By default, when a workspace is created, it is created from the topmost, or LIVE, database workspace. (Workspace names are case-sensitive, and the workspace name of the live database is spelled LIVE. The length of a workspace name must not exceed 30 characters, and the depth of the workspace hierarchy must not exceed 30 levels.) Users are included in a workspace by a GotoWorkspace operation.

Figure 1-1 shows a hierarchy of workspaces. Workspace1 and Workspace4 were formed off the LIVE database workspace; Workspace2 and Workspace3 were formed off Workspace1, and Workspace5 was formed off Workspace4. After Workspace1 was created, a user executed a GotoWorkspace operation specifying Workspace1, and then executed CreateWorkspace operations to create Workspace2 and Workspace3. A comparable sequence was followed with Workspace4 and Workspace5.

Figure 1-1 Workspace Tree

Description of "Figure 1-1 Workspace Tree"

See also Section 1.1.2.1 for a discussion of design issues in deciding whether to create a child workspace or a savepoint for certain needs

1.1.2 Using Savepoints

A savepoint is a point in the workspace to which data changes can be rolled back. Workspace Manager accomplishes the rollback by deleting the row versions that contain the unwanted changes.

An explicit savepoint is a savepoint that you create and name. You can later roll back changes in version-enabled tables to the savepoint, or you can go to the savepoint to view the state of the entire database (including versioned rows) at the time the savepoint was created. In Figure 1-2, SP1, SP2, SP3, and SP4 are explicit savepoints that were created in the workspaces indicated. (Savepoints are indicated by dashed lines in Figure 1-2.)

Figure 1-2 Savepoints

Description of "Figure 1-2 Savepoints"

In addition, implicit savepoints are created automatically whenever a new workspace is created. An implicit savepoint is needed so that the users in the child workspace get a view of the database that is frozen at the time of the workspace creation. Thus, in Figure 1-2 two implicit savepoints (SPa and SPd) are created in the LIVE workspace corresponding to Workspace1 and Workspace4 creation; two implicit savepoints (SPb and SPc) are created in Workspace1 corresponding to Workspace2 and Workspace3 creation; and one implicit savepoint (SPe) is created in Workspace4 corresponding to Workspace5 creation.

Workspace Manager uses the name LATEST to designate a logical savepoint that refers to the latest version in the workspace. LATEST is often the default when a savepoint is an optional parameter for a DBMS_WM subprogram (procedure or function).

A removable savepoint is a savepoint that can be deleted by the CompressWorkspace, CompressWorkspaceTree, and DeleteSavepoint procedures. A savepoint is removable if either of the following applies:

• It is an explicit savepoint.

• It is an implicit savepoint that does not have any child dependencies.

1.1.3 Merging and Rolling Back Workspace Changes

Workspaces can be merged or rolled back.

Merging a workspace involves applying changes made in a child workspace to its parent workspace, after which the child workspace is removed. To merge a workspace, use the MergeWorkspace procedure.

Rolling back a workspace involves deleting either all data changes (row versions) made in the workspace or all changes made after an explicit savepoint.

• To roll back all changes made in the workspace, use the RollbackWorkspace procedure.

• To roll back changes made in the workspace after a savepoint, use the RollbackToSP procedure.

  Note:

  You cannot roll back to a savepoint if any implicit savepoints were created since the specified savepoint, unless you first merge or remove the descendent workspaces that caused the implicit savepoints to be created. For example, referring to Figure 1-2 in Section 1.1.2, the user in Workspace1 cannot roll back to savepoint SP1 until Workspace3 (which caused implicit savepoint SPc to be created) is merged or removed.

A workspace cannot be rolled back when it has open database transactions. Rollback of a workspace leaves behind the workspace structure for future use; only the data in the workspace is deleted. (To completely remove a workspace, use the RemoveWorkspace procedure, as described in Section 1.1.6.)

1.1.4 Resolving Conflicts Before a Merge or Refresh Operation

When a child workspace is merged, the row changes in the child workspace are incorporated in its parent workspace; and when a child workspace is refreshed, row changes in the parent workspace are incorporated in the child workspace. When a row is changed in both the child and parent workspace, a data conflict is created. Conflicts are automatically detected when a merge or refresh operation is requested, and they are presented to the user in conflict (xxx_CONF) views. There is one conflict view for each table, as described in Section 5.45. This view lists the column values of the rows in the two workspaces involved in the conflict.

You must resolve conflicts manually by setting the workspace conflict context for the session and then using the ResolveConflicts 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 (that is, no change: keep the original data values for the row). You must resolve the conflicts before you can perform a merge (MergeWorkspace) or refresh (RefreshWorkspace) operation.

The base row is the currently visible row at the time of the first update or delete operation in the child workspace. In the case of an insert operation, the base row does not exist, except if the inserted row was previously deleted in an ancestor version of a parent workspace, the base row is that deleted row. The parent row is the currently visible row in the parent workspace at the time of the conflict resolution, and the child row is the currently visible row in the child workspace at the time of the conflict resolution.

Absence of data is not a conflict. In these cases, you can use the xxx_DIFF view (described in Section 5.46) to detect a change in one workspace when there is no row or no change to the row in the other workspace.

The general process for resolving conflicts is as follows:

1. Use the GotoWorkspace or SetConflictWorkspace procedure to set the workspace conflict context for the session.

2. Examine the xxx_CONF views (described in Section 5.45) to see what conflicts exist.

3. Execute the BeginResolve procedure.

4. Execute the ResolveConflicts procedure as often as needed: once for each affected combination of table and workspace.

5. After resolving all conflicts, execute one of the following procedures:

   • CommitResolve if you want to make permanent all changes from the preceding step. (However, the changes are not made permanent in the database until you perform a standard database commit operation and execute the MergeWorkspace or RefreshWorkspace procedure, as described in the next step.)

   • RollbackResolve to discard all changes from the preceding step. (If you discard all changes, do not perform the next step.)

6. Perform a standard database commit operation and execute the MergeWorkspace or RefreshWorkspace procedure.

1.1.5 Freezing and Unfreezing Workspaces

You can control read and write access to a workspace by freezing and unfreezing the workspace. If a workspace is frozen, the ability of users to access the workspace and to make changes to rows in version-enabled tables is restricted. You can freeze a workspace in any of the following modes: no access, read-only, and one writer only (1WRITER).

To make a workspace frozen, use the FreezeWorkspace procedure. To make a frozen workspace not frozen, use the UnfreezeWorkspace procedure.

In addition, some procedures automatically freeze one or more workspaces. Table 1-1 lists these procedures, the workspaces affected, and the mode in which the workspaces are frozen. (For explanations of the mode values, see the FreezeWorkspace procedure description in Chapter 4.)

1.1.6 Removing Workspaces

A workspace can be removed with the RemoveWorkspace procedure. RemoveWorkspace rolls back the data in a workspace and then deletes the workspace structure. An entire tree of workspaces can be removed with the RemoveWorkspaceTree procedure. This will remove the workspace and all its descendant workspaces. A workspace cannot be removed when it has users in it.

1.1.7 Using Workspace Manager Events

Several types of Workspace Manager operations can be captured as events, and can be communicated to applications through the Oracle Advanced Queuing (AQ) framework. Messaging features provided by AQ, such as asynchronous notification, persistence, propagation, access control, history, and rule-based subscription, can be used for Workspace Manager events.

Support for Workspace Manager events includes the ALLOW_CAPTURE_EVENTS Workspace Manager system parameter, the SetCaptureEvent procedure, and the WM_EVENTS_INFO metadata view.

Chapter 2 describes Workspace Manager events and explains how to use them in applications.

1.1.8 Autocommitting of Workspace Manager Operations

Many Workspace Manager operations are by default executed as autonomous database transactions that will be committed when they finish. That is, each such transaction is an independent transaction that is called from within the current database transaction, leaves the context of the calling transaction, performs the Workspace Manager operation and then automatically commits it, and then returns to the calling transaction's context and continues with that transaction. Workspace Manager (DBMS_WM) subprograms that operate in this way have an optional auto_commit parameter, which has a default value of TRUE.

For example, the CompressWorkspace procedure by default starts an autonomous transaction, compresses the workspace, commits the compression operation, and returns to the calling transaction's context, where the current database transaction continues.

However, if you want such subprograms not to start an autonomous transaction, but instead to execute in the context of the calling transaction, you can specify the auto_commit parameter with a value of FALSE. In this case, the Workspace Manager operation is executed as part of the current database transaction; and if there is no current open transaction, the Workspace Manager operation starts a new transaction. In either case, the Workspace Manager operation does not take effect until that transaction ends with a commit operation. For example, if you call the CompressWorkspace procedure with the auto_commit parameter specified as FALSE, the workspace is not compressed until the transaction is committed; and if the transaction is rolled back, the workspace is not compressed.

Note that if you specify FALSE for the auto_commit parameter, you must remember to commit or roll back the transaction explicitly.

If the auto_commit parameter value is TRUE and any open transactions exist, the following considerations apply:

• For the CompressWorkspace and CompressWorkspaceTree procedures, an exception is raised if there is any open transaction.

• For all other Workspace Manager procedures, an exception is raised if there is an open transaction in a parent or child workspace of any table that needs to be modified.

1.1.9 Continually Refreshed Workspaces

A continually refreshed workspace is a workspace that is automatically refreshed from its parent workspace whenever data changes are committed in the parent workspace or are merged into the parent workspace from another child workspace. You do not need to call the RefreshWorkspace procedure for a continually refreshed workspace.

Any workspace in a branch of the workspace tree can be continually refreshed. A child workspace can be a continually refreshed workspace, regardless of whether its parent workspace is continually refreshed. However, if a parent workspace is a continually refreshed workspace, its child workspaces must also be continually refreshed.

To create a continually refreshed workspace, specify TRUE for the isrefreshed parameter in the call to the CreateWorkspace procedure. See the Usage Notes for the CreateWorkspace procedure for rules that apply to the creation of a continually refreshed workspace.

To change a workspace that is not continually refreshed to be continually refreshed, use the ChangeWorkspaceType procedure.

If a workspace is not continually refreshed, you must call the RefreshWorkspace procedure whenever you want to ensure that data changes in its parent workspace are visible in the workspace.

1.1.10 Multiparent Workspaces

A multiparent workspace is a child workspace that has two or more parent workspaces. A workspace is initially created with a single parent workspace. However, if the need arises, you can add other workspaces as parent workspaces to an existing workspace, thus making it a multiparent workspace. The multiparent workspace can see data from all of its parent workspaces and their ancestor workspaces, and it can be merged with and refreshed from its parent workspaces.

Figure 1-3 shows the same hierarchy of workspaces in Figure 1-1, except that Workspace3 is now a multiparent workspace with two parent workspaces: Workspace1 and Workspace4.

Figure 1-3 Multiparent Workspace in a Workspace Hierarchy

Description of "Figure 1-3 Multiparent Workspace in a Workspace Hierarchy"

A multiparent workspace is also called a multiparent leaf workspace. Thus, in Figure 1-3, Workspace3 is a multiparent leaf workspace. The nearest common ancestor of all parent workspaces of a multiparent lead workspace is called the multiparent root workspace. In Figure 1-3, the LIVE workspace is the multiparent root workspace of Workspace3. All of the workspaces in the directed acyclic graph (DAG) formed as a result of adding parent workspaces as parents of a leaf workspace are called multiparent graph workspaces. In Figure 1-3, Workspace1, Workspace4, and Workspace3 are the multiparent graph workspaces.

Multiparent workspaces are allowed only if the ALLOW_MULTI_PARENT_WORKSPACE Workspace Manager system parameter is set to ON. In addition, for a continually refreshed workspace to be a multiparent workspace, the CR_WORKSPACE_MODE Workspace Manager system parameter must be set to PESSIMISTIC_LOCKING; and for a workspace that is not continually refreshed to be a multiparent workspace, the NONCR_WORKSPACE_MODE Workspace Manager system parameter must be set to PESSIMISTIC_LOCKING. For information about Workspace Manager system parameters, see Section 1.5.

To create a multiparent workspace, use the AddAsParentWorkspace procedure. To remove a workspace as a parent of a multiparent workspace, use the RemoveAsParentWorkspace procedure. To grant and revoke privileges on multiparent graph workspaces, use the GrantGraphPriv and RevokeGraphPriv procedures, respectively. These procedures are described in Chapter 4.

Workspace Manager provides the following static data dictionary views (described in Chapter 5) to store information about multiparent workspaces:

• USER_MP_GRAPH_WORKSPACES and ALL_MP_GRAPH_WORKSPACES contain information about multiparent graph workspaces.

• USER_MP_PARENT_WORKSPACES and ALL_MP_PARENT_WORKSPACES contain information about parent workspaces of multiparent leaf workspaces.

1.1.11 Infrastructure for Version-Enabling of Tables

When you version-enable a table using the EnableVersioning procedure, Workspace Manager automatically performs operations and creates data structures that are invisible to non-DBA users, but that permit Workspace Manager to function. Some of the information maintained by Workspace Manager is stored in the static data dictionary views described in Chapter 5, and some is stored in system data structures not accessible by users.

When a table is version-enabled, Workspace Manager renames the table to <table-name>_LT, and it adds several columns to this table to store versioning metadata. Note that users and applications should not specify the <table-name>_LT table in SQL statements; they should continue to specify the original table name (<table-name>). (If you ever need to find the name of the <table_name>_LT table associated with a version-enabled table, or if you want to find out if a table is version-enabled by checking for the existence of a <table_name>_LT table, use the GetPhysicalTableName function.)

Workspace Manager also creates a view on the original table (<table-name>), as well as INSTEAD OF triggers on the view for insert, update, and delete operations. When an application executes a statement to insert, update, or delete data in a version-enabled table, the appropriate INSTEAD OF trigger performs the actual operation. When the view is accessed, it uses the workspace metadata to show only the row versions relevant to the current workspace of the user.

Because Workspace Manager uses the original object name when it creates infrastructure objects, the effective maximum length of the name for some kinds of objects is shorter than the maximum permitted by Oracle Database. Table 1-2 provides guidelines for the maximum name length for version-enabled tables and related objects. (See also the information in Section 1.15 about reserved words and characters for certain names.)

Workspace Manager does not support the RETURNING clause with INSERT, MERGE, or UPDATE statements on version-enabled tables. This restriction is caused by the fact that Workspace Manager creates views with INSTEAD OF triggers on version-enabled tables, and Oracle Database does not support the RETURNING clause on views that have INSTEAD OF triggers defined on them.

1.1.12 Creation of Row Versions and Historical Copies

This section describes the process by which Workspace Manager creates new row versions and maintains historical copies of old versions.

A new row version is created in a version-enabled table when you do either of the following:

• Create an explicit savepoint in the current workspace, and update the row.

• Create a child workspace (using the CreateWorkspace procedure) under the current workspace, thus creating an implicit savepoint in the current workspace; execute the GotoWorkspace procedure to go to the child workspace; and update the row.

Any subsequent update of the current row overwrites the current row version, unless history is enabled on the table or another savepoint is created.

• If history is enabled without overwrite (VIEW_WO_OVERWRITE), each update of the current row version creates a full copy of the current row version with the changes and with a timestamp based on the transaction time. This copy becomes the new current row version.

• If history is enabled with overwrite (VIEW_W_OVERWRITE), each update of the current row version overwrites the current row version and updates the transaction timestamp.

• If a savepoint is created in the workspace, the next change to the row creates a new version, and the history cycle begins again.

Row versions created in a workspace are not visible from that workspace until you execute the MergeWorkspace or MergeTable procedure.

When you execute the MergeWorkspace procedure for a child workspace, only the current row version in the child workspace is merged into the parent workspace. If you specify the remove_workspace parameter as TRUE, any intermediate row versions in the child workspace are deleted when the child workspace is removed. To retain all intermediate versions created in the child workspace, the remove_workspace parameter value must be FALSE (the default).

When you execute the CompressWorkspace procedure on a child workspace to eliminate intermediate savepoints, you can also remove the associated historical copies of that row version. If you do not remove these copies, they are associated with the next version.

Intermediate row versions can only be selected for read-only access. To select an intermediate row version for read-only access, go to the workspace (GotoWorkspace procedure) if you are not already in it, and execute either the GotoDate procedure to set the session context to a historical time or the GotoSavepoint procedure to set the session context to a specific savepoint. Subsequent SELECT statements will select, for read-only access, the latest row version as of the specified date or savepoint.

1.1.13 Workspace Manager Schema, Metadata, and Package

Workspace Manager creates a user named WMSYS. The WMSYS schema is used to store all the metadata information for Workspace Manager. A PL/SQL package with the public synonym DBMS_WM contains the Workspace Manager subprograms (procedures and functions).

The following privileges are granted to the PUBLIC user group:

• SELECT privilege on Workspace Manager static data dictionary views (described in Chapter 5)

• EXECUTE privilege on the DBMS_WM package (described in Chapter 4)

1.3.1 Exclusive Locking and Row Versions

The timing of an exclusive lock with respect to an update operation in a child workspace can affect which version, if any, of the row can be updated in a parent workspace. For example, when a table is version-enabled in the LIVE workspace, each original row is assigned version 0. Assume that a workspace named W1 is created as a child of the LIVE workspace. When workspace W1 is created, the following things happen:

• Version 1 is assigned to the LIVE workspace (but no additional row is created).

• Version 2 is assigned to workspace W1 (but no additional row is created). Queries in workspace W1 still return version 0 of the row that is in the LIVE workspace.

Using this example, if a user in workspace W1 places an exclusive lock on a row before it updates the row, only that user in workspace W1 can update the row. Specifically:

• Version 0 of the row is locked, preventing any update of the row from any workspace until the row is unlocked.

• The lock can be placed from workspace W1 (or a descendent workspace of W1) because version 0 is the current physical row for the workspace.

• When a user in workspace W1 updates the row, a new row (version 2) is created that is visible only from workspace W1 and any of its child workspaces.

However, if the row is not locked in the LIVE workspace and if a user in workspace W1 updates the row and then places an exclusive lock on the row, a user in the LIVE workspace can update the row. Specifically:

• A new row (version 2) is created that is visible only from workspace W1 and any of its child workspaces.

• Version 2 of the row is locked. No user in workspace W1 other than the user that placed the lock, or no user in any child workspace of W1, can update the row or create a new version of the row.

• Version 0 of the row in the LIVE workspace is not locked. If a user in the LIVE workspace or a sibling workspace of W1 updates the row, a new version (version 1) of the row is created. (Version 0 is not locked because it is no longer the current version of the row for users in workspace W1; rather, version 2 is the current version of the row in that workspace.)

In other words, an exclusive lock after an update does not lock previous versions of the row in workspaces above the locking workspace in the workspace tree or in other branches of the workspace tree.

1.9.1 Referential Integrity Support

Version-enabled tables can have referential integrity constraints, including constraints with the CASCADE and RESTRICT options; however, the following considerations and restrictions apply:

• If the parent table in a referential integrity relationship is version-enabled, the child table must be version-enabled also. (The child table is the one on which the constraint is defined.) For example, consider the following EMPLOYEE and DEPARTMENT table definitions, with a foreign key constraint added after the creation (that is, the dept_id value in each EMPLOYEE row must match an existing dept_id value in a DEPARTMENT row).

  CREATE TABLE employee (
    employee_id NUMBER,
    last_name VARCHAR2(32),
    first_name VARCHAR2(32),
    dept_id NUMBER);
  CREATE TABLE department (
    dept_id NUMBER,
    name VARCHAR2(32);
  ALTER TABLE employee ADD CONSTRAINT emp_forkey_deptid
    FOREIGN KEY (dept_id) REFERENCES department (dept_id)
    ON DELETE CASCADE;
  
  

  In this example, DEPARTMENT is considered the parent and EMPLOYEE is considered the child in the referential integrity relationship; and if DEPARTMENT is version-enabled, EMPLOYEE must be version-enabled also. In this relationship definition, when a DEPARTMENT row is deleted, all its child rows in the EMPLOYEE table are deleted (cascading delete operation).

• A child table in a referential integrity relationship is allowed to be version-enabled without the parent table being version-enabled.

• The foreign key in a child table must refer to the primary key in the parent table.

• Primary key values in the parent table cannot be updated. For example, if DEPARTMENT is the parent table and EMPLOYEE is the child table, you cannot change the department ID of a department.

• Multilevel referential integrity constraints are permitted on version-enabled tables. For example, the table EMPLOYEE(emp_id, dept_id) could have the constraint that the department ID must exist in the table DEPARTMENT(dept_id, dept_name, loc_id); and the table DEPARTMENT(dept_id, dept_name, loc_id) could have the constraint that the location ID must exist in the table LOCATION(loc_id, loc_name). However, all tables that are involved in multilevel referential integrity constraints must be version-enabled and version-disabled together, unless all the referential integrity constraints involved have the Restrict rule. If all the constraints involved have the Restrict rule, you can version-enable the tables either all together or one at a time with child tables preceding their parent tables. The table names must be passed as a comma-delimited list to the EnableVersioning and DisableVersioning procedures.

Workspace Manager uses the static data dictionary views ALL_WM_RIC_INFO and USER_WM_RIC_INFO (described in Chapter 5) to hold information pertinent to referential integrity support.

If you need to add, drop, enable, or disable a referential integrity constraint that involves two tables, it is more convenient if you perform the operation before version-enabling the tables. However, you can add, drop, enable, or disable a referential integrity constraint that involves two version-enabled tables if you follow these steps:

1. Begin a DDL session specifying the parent table.

2. Begin a DDL session specifying the child table.

3. Alter the <table-name>_LTS table for the child table to add the foreign key constraint. (See Section 1.8 for information about <table-name>_LTS tables and performing DDL operations on version-enabled tables.)

4. Commit the DDL changes specifying the child table.

5. Commit the DDL changes specifying the parent table.

Example 1-2 adds a foreign key constraint. Assume that the EMPLOYEE and DEPARTMENT tables are version-enabled and are defined as follows:

EMPLOYEE(emp_id number  primary key, dept_id number)
DEPARTMENT(dept_id number  primary key, dept_name varchar2(30))

-- Begin a DDL session on the parent table.
DBMS_WM.BeginDDL('DEPARTMENT'); 

-- Begin a DDL session on the child table.
DBMS_WM.BeginDDL('EMPLOYEE'); 

-- Add the constraint between EMPLOYEE_LTS and DAPATMENT_LTS.
ALTER TABLE employee_lts ADD CONSTRAINT employee_fk FOREIGN KEY (dept_id)
   REFERENCES department_lts(dept_id);

-- Commit DDL on the child table (transfers the constraint on employee_lts
-- to employee and drops employee_lts). 
EXECUTE DBMS_WM.CommitDDL('EMPLOYEE'); 

-- Commit DDL on the parent table (drops the department_lts table). 
EXECUTE DBMS_WM.CommitDDL('DEPARTMENT'); 

1.9.2 Unique Constraints

Tables with unique constraints defined on them can be version-enabled. The following are supported:

• UNIQUE constraint on a single column or multiple columns

• Unique index on a single column or multiple columns

• Functional unique index on the table

The treatment of null values is the same for version-enabled tables as for tables that are not version-enabled.

Workspace Manager uses the following static data dictionary views (described in Chapter 5) to hold information pertinent to support for unique constraints:

• ALL_WM_CONSTRAINTS and USER_WM_CONSTRAINTS contain information about columns in unique constraints on version-enabled tables.

• ALL_WM_CONS_COLUMNS and USER_WM_CONS_COLUMNS contain information about constraints on version-enabled tables.

• ALL_WM_IND_COLUMNS and USER_WM_IND_COLUMNS contain information about indexes used for enforcing unique constraints on version-enabled tables.

• ALL_WM_IND_EXPRESSIONS and USER_WM_IND_EXPRESSIONS contain information about functional expressions on functional unique indexes on version-enabled tables.

1.9.3 SET NULL Constraints

SET NULL constraints are not supported by Workspace Manager. If a table has any SET NULL constraints, they are converted to the RESTRICT option when the table is version-enabled.

For example, the constraint ON DELETE SET NULL is converted to ON DELETE RESTRICT.

1.14.1 Locking Considerations with Topologies

To lock or unlock rows in tables associated with a topology, you must specify the topology name as the table_name parameter to the LockRows or UnlockRows procedure, and you must identify the window containing the rows by using the Xmin, Ymin, Xmax, and Ymax parameters. You must also not specify the where_clause parameter. For example:

EXECUTE DBMS_WM.LockRows (workspace => 'ws1', table_name => 'xyz_topo', Xmin => 0.1, Ymin => 0.1,  Xmax => 0.5, Ymax => 0.5 );

The preceding example puts version locks on all the rows of the specified topology contained in the specified window. To edit the elements of a topology in a workspace (including the LIVE workspace), follow these steps:

1. Invoke the LockRows procedure to put version locks on all the elements of the topology contained in a window of interest.

2. Invoke the Oracle Spatial Topology Java client loadWindow method for the same window of interest.

1.14.2 Additional Considerations with Topologies

The following additional considerations apply to using Workspace Manager with Spatial topologies:

• You must invoke the SDO_TOPO.INITIALIZE_METADATA procedure at least once on a topology before you version-enable the tables associated with the topology. (You can also invoke the SDO_TOPO.INITIALIZE_METADATA procedure as needed after version-enabling a topology.)

• Do not use the MergeTable, RefreshTable, or RollbackTable procedure on a version-enabled table associated with a topology. Instead, use the MergeWorkspace, RefreshWorkspace, or RollbackWorkspace procedure to merge, refresh, or roll back tables associated with a topology.

1.16.1 Table Management Subprograms

Table management subprograms enable and disable workspace management on a table, and perform other table-related operations.

Table 1-7 shows the subprograms available for table management.

1.16.2 Workspace Management Subprograms

Workspace management subprograms perform operations on workspaces.

Table 1-8 shows the subprograms available for workspace management.

1.16.3 Savepoint Management Subprograms

Savepoint management subprograms perform operations related to savepoints.

Table 1-9 shows the subprograms available for savepoint management.

1.16.4 Privilege Management Subprograms

Privilege management subprograms grant and revoke Workspace Manager privileges.

Table 1-10 shows the subprograms available for privilege management.

1.16.5 Lock Management Subprograms

Lock management subprograms control Workspace Manager locking.

Table 1-11 shows the subprograms available for lock management.

1.16.6 Conflict Management Subprograms

Conflict management subprograms detect and resolve conflicts between workspaces.

Table 1-12 shows the subprograms available for conflict management.

1.16.7 Replication Support Subprograms

Replication support subprograms provide support for Oracle replication in a Workspace Manager environment. For information about using replication, see Appendix C.

Table 1-13 shows the subprograms available for replication support.

1.16.8 Bulk Load Support Subprograms

Bulk load support subprograms enable SQL*Loader to be used for bulk loading data into version-enabled tables, as explained in Section 1.7.

Table 1-14 shows the subprograms available for bulk loading support.

1.17.1 Example: Marketing Budget Options

In Example 1-3, a soft drink (cola) manufacturer has four products, each with a marketing manager and a marketing budget. Because of an exceptional opportunity for growth in the market for one product (cola_b), the company wants to do what-if analyses involving different managers and budget amounts.

-------------------------------------------------------------------
-- INITIAL SET-UP
-------------------------------------------------------------------
-- Create the user for schema objects.
CREATE USER wm_developer IDENTIFIED BY wm_developer;

-- Grant regular privileges.
GRANT connect, resource to wm_developer;
GRANT create table to wm_developer;

-- Grant WM-specific privileges (with grant_option = YES).
EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE,
 CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE',
 'wm_developer', 'YES');

---------------------------------------------------------------------------
-- CREATE AND POPULATE DATA TABLE --
---------------------------------------------------------------------------
CONNECT wm_developer/wm_developer

-- Cleanup: remove B_focus_2 workspace if it exists from previous run.
EXECUTE DBMS_WM.RemoveWorkspace ('B_focus_2');

-- Create a table for the annual marketing budget for
-- several cola (soft drink) products.
-- Each row will contain budget data for a specific
-- product. Note: This table does not reflect recommended
-- database design. (For example, a manager ID should
-- be used, not a name.) It is deliberately oversimplified
-- for purposes of illustration.

CREATE TABLE cola_marketing_budget (
  product_id NUMBER PRIMARY KEY,
  product_name VARCHAR2(32),
  manager VARCHAR2(32),  -- Here a name, just for simplicity
  budget NUMBER  -- Budget in millions of dollars. Example: 3 = $3,000,000.
);

-- Version-enable the table. Specify hist option of VIEW_WO_OVERWRITE so that
-- the COLA_MARKETING_BUDGET_HIST view contains complete history information
-- about data changes.
EXECUTE DBMS_WM.EnableVersioning ('cola_marketing_budget', 'VIEW_WO_OVERWRITE');

INSERT INTO cola_marketing_budget VALUES(
  1,
  'cola_a',
  'Alvarez',
  2.0
);
INSERT INTO cola_marketing_budget VALUES(
  2,
  'cola_b',
  'Baker',
  1.5
);
INSERT INTO cola_marketing_budget VALUES(
  3,
  'cola_c',
  'Chen',
  1.5
);
INSERT INTO cola_marketing_budget VALUES(
  4,
  'cola_d',
  'Davis',
  3.5
);
COMMIT;

-- Relevant data values now in LIVE workspace:
-- 1, cola_a, Alvarez, 2.0
-- 2, cola_b, Baker, 1.5
-- 3, cola_c, Chen, 1.5
-- 4, cola_d, Davis, 3.5

---------------------------------------------------------------------------
-- CREATE WORKSPACES --
---------------------------------------------------------------------------
-- Create workspaces for the following scenario: a major marketing focus
-- for the cola_b product. Managers and budget amounts for each
-- product can change, but the total marketing budget cannot grow.
--
-- One scenario (B_focus_1) features a manager with more expensive 
-- plans (which means more money taken from other products' budgets).
-- The other scenario (B_focus_2) features a manager with less expensive 
-- plans (which means less money taken from other products' budgets).
--
-- Two workspaces (B_focus_1 and B_focus_2) are created as child workspaces 
-- of the LIVE database workspace.

EXECUTE DBMS_WM.CreateWorkspace ('B_focus_1');
EXECUTE DBMS_WM.CreateWorkspace ('B_focus_2');

---------------------------------------------------------------------------
-- WORK IN FIRST WORKSPACE --
---------------------------------------------------------------------------
-- Enter the B_focus_1 workspace and change the cola_b manager to Beasley and 
-- raise the cola_b budget amount by 1.5 to bring it to 3.0. Reduce all other 
-- products' budget amounts by 0.5 to stay within the overall budget.

EXECUTE DBMS_WM.GotoWorkspace ('B_focus_1');
UPDATE cola_marketing_budget
  SET manager = 'Beasley' WHERE product_name = 'cola_b';
UPDATE cola_marketing_budget
  SET budget = 3 WHERE product_name = 'cola_b';
UPDATE cola_marketing_budget
  SET budget = 1.5 WHERE product_name = 'cola_a';
UPDATE cola_marketing_budget
  SET budget = 1 WHERE product_name = 'cola_c';
UPDATE cola_marketing_budget
  SET budget = 3 WHERE product_name = 'cola_d';
COMMIT;

-- Relevant data values now in B_focus_1 workspace::
-- 1, cola_a, Alvarez, 1.5
-- 2, cola_b, Beasley, 3.0
-- 3, cola_c, Chen, 1.0
-- 4, cola_d, Davis, 3.0

-- Freeze this workspace to prevent any changes until workspace is unfrozen.
-- However, first go to the LIVE workspace, because a workspace cannot be frozen
-- if any users (including you) are in it.
EXECUTE DBMS_WM.GotoWorkspace ('LIVE');
EXECUTE DBMS_WM.FreezeWorkspace ('B_focus_1');

---------------------------------------------------------------------------
-- CREATE ANOTHER SCENARIO IN SECOND WORKSPACE --
---------------------------------------------------------------------------
-- Enter the B_focus_2 workspace and change the cola_b manager to Burton and 
-- raise the cola_b budget amount by 0.5 to bring it to 2.0. Reduce only the 
-- cola_d amount by 0.5 to stay within the overall budget.

EXECUTE DBMS_WM.GotoWorkspace ('B_focus_2');
UPDATE cola_marketing_budget
  SET manager = 'Burton' WHERE product_name = 'cola_b';
UPDATE cola_marketing_budget
  SET budget = 2 WHERE product_name = 'cola_b';
UPDATE cola_marketing_budget
  SET budget = 3 WHERE product_name = 'cola_d';
COMMIT;

-- Relevant data values now in B_focus_2 workspace::
-- 1, cola_a, Alvarez, 2.0 (no change from LIVE)
-- 2, cola_b, Burton, 2.0
-- 3, cola_c, Chen, 1.5 (no change from LIVE)
-- 4, cola_d, Davis, 3.0 (same manager, new budget)

-- Create a savepoint (B_focus_2_SP1), then change scenario to 
-- raise cola_b budget and reduce cola_d budget by 0.5 each.

EXECUTE DBMS_WM.CreateSavepoint ('B_focus_2', 'B_focus_2_SP1');
UPDATE cola_marketing_budget
  SET budget = 2.5 WHERE product_name = 'cola_b';
UPDATE cola_marketing_budget
  SET budget = 2.5 WHERE product_name = 'cola_d';
COMMIT;

-- Relevant data values now in B_focus_2 workspace:
-- 1, cola_a, Alvarez, 2.0 (no change from LIVE)
-- 2, cola_b, Burton, 2.5 
-- 3, cola_c, Chen, 1.5 (no change from LIVE)
-- 4, cola_d, Davis, 2.5 (same manager, new budget)

-- Discard this scenario; roll back to row values at the time savepoint 
-- B_focus_2_SP1 was created. First, though, get out of the workspace
-- so it can be rolled back (no users in it).

EXECUTE DBMS_WM.GotoWorkspace ('LIVE');
EXECUTE DBMS_WM.RollbackToSP ('B_focus_2', 'B_focus_2_SP1');

-- Go back to the B_focus_2 workspace and display current values 
-- (should include budget of 2 for cola_b and 3 for cola_d).
EXECUTE DBMS_WM.GotoWorkspace ('B_focus_2');
SELECT * FROM cola_marketing_budget;

---------------------------------------------------------------------------
-- SELECT SCENARIO AND UPDATE DATABASE --
---------------------------------------------------------------------------
-- Assume that you have decided to adopt the scenario of the second 
-- workspace (B_focus_2) using that workspace's current values.

-- First go to the LIVE workspace, because a workspace cannot be removed
-- or merged if any users (including you) are in it.
EXECUTE DBMS_WM.GotoWorkspace ('LIVE');

-- Unfreeze the first workspace and remove it to discard any changes there.
EXECUTE DBMS_WM.UnfreezeWorkspace ('B_focus_1');
EXECUTE DBMS_WM.RemoveWorkspace ('B_focus_1');

-- Apply changes in the second workspace to the LIVE database workspace.
-- Note that the workspace is not removed by default after MergeWorkspace.
EXECUTE DBMS_WM.MergeWorkspace ('B_focus_2');

-- Display the current data values (which are in the LIVE database 
-- workspace, which is the only workspace currently existing).
SELECT * FROM cola_marketing_budget;

---------------------------------------------------------------------------
-- DISABLE VERSIONING --
---------------------------------------------------------------------------
-- Disable versioning on the table because you are finished testing scenarios.
-- Set force parameter to TRUE if you want to force the disabling even
-- if changes were made in a non-LIVE workspace.

EXECUTE DBMS_WM.DisableVersioning ('cola_marketing_budget', TRUE);

1.17.2 Example: Warehouse Expansion Options

In Example 1-4, a company that uses the Oracle sample schemas decided that it needs additional warehouse space. It wants to consider two scenarios: a single large warehouse in Town A, and two smaller warehouses in Town B and Town C that together offer more total storage capacity. There are potential advantages and disadvantages to each scenario, and financial and legal issues to be resolved with each. Later, the company decides that it might need even more warehouse space under each scenario, so it wants to consider the same additional warehouse in each scenario.

Example 1-4 creates a workspace for each scenario; and within each workspace it creates a savepoint before adding an extra new warehouse to the table, because the company might decide not to use the extra warehouse. The warehouse rows are stored on the OE.WAREHOUSES table, which is part of the Oracle sample schemas.

-------------------------------------------------------------------
-- INITIAL SET-UP
-------------------------------------------------------------------
-- Clean up from any previous running of this procedure.
DROP USER wm_developer CASCADE;

-- Create the user for schema objects.
CREATE USER wm_developer IDENTIFIED BY wm_developer;

-- Grant regular privileges.
GRANT connect, resource TO wm_developer;
GRANT create table TO wm_developer;

-- Grant privileges on tables to be modified.
GRANT select, insert, delete, update ON oe.warehouses TO wm_developer; 
GRANT select, insert, delete, update ON hr.locations TO wm_developer; 

-- Grant WM-specific privileges (with grant_option = YES).
EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE,
 CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE',  
 'wm_developer', 'YES');

-- WM_ADMIN_ROLE required to version-enable a table in another schema.
GRANT wm_admin_role TO wm_developer;

-- Create rows for new locations, since a valid location ID is needed for each
-- proposed new warehouse.
INSERT INTO hr.locations VALUES 
   (4000, '123 Any Street', '01234', 'Town A', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4100, '456 Some Street', '01235', 'Town B', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4200, '789 Other Street', '01236', 'Town C', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4300, '1 Yetanother Street', '01237', 'Town D', 'MA', 'US');

---------------------------------------------------------------------------
-- CREATE AND VERSION-ENABLE THE DATA TABLE --
---------------------------------------------------------------------------
CONNECT wm_developer/wm_developer
set echo on
set serveroutput on

-- Version-enable the OE.WAREHOUSES table. Specify hist option of 
-- VIEW_WO_OVERWRITE so that the WAREHOUSES_HIST view contains 
-- complete history information about data changes. However, because 
-- OE.WAREHOUSES is the parent table in a referential integrity constraint
-- with OE.INVENTORIES, you must also version-enable that table.

EXECUTE DBMS_WM.EnableVersioning ('OE.WAREHOUSES, OE.INVENTORIES', hist => 'VIEW_WO_OVERWRITE');

------------------------------------------------------------------------
-- CREATE AND USE WORKSPACES --
------------------------------------------------------------------------
-- The company has decided that it needs additional warehouse space. 
-- It wants to consider two scenarios: a single large warehouse in Town A,
-- and two smaller warehouses in Town B and Town C that together offer more
-- total storage capacity. There are potential advantages and disadvantages 
-- to each scenario, and financial and legal issues to be resolved with each.
--
-- Later, the company decides that it might need even more warehouse
-- space under each scenario, so it wants to consider the same additional 
-- warehouse in each scenario.

-- Create a workspace for each scenario, with both created as child 
-- workspaces of the LIVE database workspace.
-- In workspace large_warehouse, add one row for the single large warehouse.
-- In workspace smaller_warehouses, add two rows, one for each warehouse.
--
-- Also, within each workspace create a savepoint before adding the
-- extra warehouse, because the company might decide it does not
-- need the warehouse.

EXECUTE DBMS_WM.CreateWorkspace (workspace => 'large_warehouse');
EXECUTE DBMS_WM.CreateWorkspace (workspace => 'smaller_warehouses');

-- Set up the first scenario: Go to the large_warehouse workspace and first add
-- one row for a warehouse.

EXECUTE DBMS_WM.GotoWorkspace (workspace => 'large_warehouse');

INSERT INTO oe.warehouses VALUES (10, NULL, 'Town A', 4000,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00703, 42.27099, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Owned</Building> 
<Area>100000</Area> 
<Docks>2</Docks> 
<DockType>Side load</DockType> 
<WaterAccess>Y</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Lot</Parking> 
<VClearance>15 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 10; 

COMMIT;

-- Create a savepoint so that you can, if necessary, roll back to the point
-- before the extra warehouse was added.
EXECUTE DBMS_WM.CreateSavepoint ('large_warehouse', 'large_warehouse_add_wh');

-- Add another warehouse for this scenario.
INSERT INTO oe.warehouses VALUES (11, NULL, 'Town D', 4300,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00707, 42.35226, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>55000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>N</RailAccess> 
<Parking>Street</Parking> 
<VClearance>10 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 11; 

COMMIT;

-- Freeze this workspace to prevent any changes until the workspace is unfrozen.
-- However, first go to the LIVE workspace, because a workspace cannot be frozen
-- if any users (including you) are in it.
EXECUTE DBMS_WM.GotoWorkspace ('LIVE');
EXECUTE DBMS_WM.FreezeWorkspace ('large_warehouse');

-- Set up the second scenario: Go to the smaller_warehouses workspace and first 
-- add two rows for the smaller warehouses.

EXECUTE DBMS_WM.GotoWorkspace ('smaller_warehouses');

INSERT INTO oe.warehouses VALUES (10, NULL, 'Town B', 4100,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.02439, 42.28628, NULL), NULL, NULL)); 

INSERT INTO oe.warehouses VALUES (11, NULL, 'Town C', 4200,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-70.97980, 42.37961, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Owned</Building> 
<Area>60000</Area> 
<Docks>1</Docks> 
<DockType>Side load</DockType> 
<WaterAccess>Y</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Lot</Parking> 
<VClearance>15 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 10; 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>550000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Street</Parking> 
<VClearance>12 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 11; 

COMMIT;

-- Create a savepoint so that you can, if necessary, roll back to the point
-- before the extra warehouse was added.
EXECUTE DBMS_WM.CreateSavepoint ('smaller_warehouses', 'smaller_warehouses_add_wh');

-- Add the extra warehouse for this scenario.
INSERT INTO oe.warehouses VALUES (12, NULL, 'Town D', 4300,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00707, 42.35226, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>55000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>N</RailAccess> 
<Parking>Street</Parking> 
<VClearance>10 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 12; 

COMMIT;

---------------------------------------------------------------------------
-- SELECT A SCENARIO, AND APPLY IT --
---------------------------------------------------------------------------
-- Later, the company makes its decisions:
-- 1. Add two smaller warehouses.
-- 2. Do not add the extra warehouse (that is, no third new warehouse).
-- Consequently, you need to discard the first scenario (large_warehouse
-- workspace) completely, discard the warehouse addition in the second
-- scenario (roll back to smaller_warehouses_add_wh savepoint), and 
-- apply the second scenario.

-- First go to the LIVE workspace, because a workspace cannot be removed
-- or merged if any users (including you) are in it.
EXECUTE DBMS_WM.GotoWorkspace ('LIVE');

-- Unfreeze the first workspace and remove it to discard any changes there.
EXECUTE DBMS_WM.UnfreezeWorkspace ('large_warehouse');
EXECUTE DBMS_WM.RemoveWorkspace ('large_warehouse');

-- Rollback the workspace for the second scenario to the savepoint created
-- before the extra warehouse was added.
EXECUTE DBMS_WM.RollbackToSP ('smaller_warehouses', 'smaller_warehouses_add_wh');

-- Apply changes in the smaller_warehouses workspace to the LIVE database 
-- workspace; use the remove_workspace parameter to remove the 
-- smaller_warehouses workspace after the merge.
EXECUTE DBMS_WM.MergeWorkspace ('smaller_warehouses', remove_workspace => TRUE);

-- The OE.WAREHOUSES table now has the desired data (two additional warehouses
-- from the smaller_warehouses scenario). Display the IDs and names just to be
-- sure.
SELECT warehouse_id, warehouse_name FROM oe.warehouses 
   ORDER BY warehouse_id;

-- Disable versioning on the table because you are finished testing scenarios.
-- Set the force parameter to TRUE to force disabling even though changes 
-- were made in a non-LIVE workspace. You must also version-disable
-- the other tables previously version-enabled (along with OE.WAREHOUSES).

EXECUTE DBMS_WM.DisableVersioning ('OE.WAREHOUSES, OE.INVENTORIES', force => TRUE);

-- Clean up by deleting the rows that were added to the OE.WAREHOUSES table.
DELETE FROM oe.warehouses WHERE warehouse_id >= 10;

-- Clean up by deleting the locations that were added.
DELETE FROM hr.locations WHERE location_id >= 4000;

SELECT warehouse_id, warehouse_name FROM oe.warehouses 
   ORDER BY warehouse_id;

WAREHOUSE_ID WAREHOUSE_NAME                                                     
------------ -----------------------------------                                
           1 Southlake, Texas                                                   
           2 San Francisco                                                      
           3 New Jersey                                                         
           4 Seattle, Washington                                                
           5 Toronto                                                            
           6 Sydney                                                             
           7 Mexico City                                                        
           8 Beijing                                                            
           9 Bombay                                                             
          10 Town B                                                             
          11 Town C