New and Changed Features

This section describes new and changed Workspace Manager features for Oracle Database 10g Release 1 (10.1).

System Parameters for Workspace Manager

Workspace Manager now provides a set of system parameters that allow a user with the WM_ADMIN_ROLE role to enforce global Workspace Manager-specific settings for the database. These system parameters are described in Section 1.5.

Multiparent Workspaces

A child workspace can now have two or more parent workspaces, in which case it becomes a multiparent workspace. A 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. Multiparent workspaces are described in Section 1.1.10.

Workspace Manager Events

Several types of Workspace Manager operations can be captured as events, and can be communicated to applications through the Oracle Advanced Queueing framework. Support for Workspace Manager events includes the ALLOW_CAPTURE_EVENTS 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.

Valid Time Support

You can specify a valid time, also known as effective dating, for use with version-enabled tables. Workspace Manager valid time support is explained in Chapter 3.

Exporting and Importing Data

The Export procedure exports data from a version-enabled table (all rows, or as limited by any combination of several parameters) to a staging table.

The Import procedure imports data from a staging table into a version-enabled table.

These procedures are described in Chapter 4.

Bulk Loading into Version-Enabled Tables

You can use SQL*Loader to perform bulk loading into version-enabled tables, but you must also use some new Workspace Manager procedures, and some restrictions apply, as explained in Section 1.7.

Compression, Commit Operations, and Batch Sizes

The new optional parameters commit_in_batches and batch_size are available for the CompressWorkspace, CompressWorkspaceTree, and DeleteSavepoint procedures.

The new WM_COMPRESS_BATCH_SIZES and WM_COMPRESSIBLE_TABLES metadata views (described in Chapter 5) provide information related to compression options. The new SetCompressWorkspace procedure populates the WM_COMPRESSIBLE_TABLES view.

Continually Refreshed Workspaces: New Procedure

The ChangeWorkspaceType procedure (described in Chapter 4) lets you change a workspace that is not continually refreshed to be continually refreshed.

Physical Table Names (Infrastructure): New Procedure

The GetPhysicalTableName function (described in Chapter 4) lets you find the name of the <table_name>_LT table associated with a version-enabled table. It also lets you check whether or not a table is version-enabled by checking for the existence of a <table_name>_LT table. (The Workspace Manager infrastructure and its use of <table_name>_LT tables is explained in Section 1.1.11.)

DDL Support

Support for data definition language (DDL) operations on version-enabled tables has been enhanced. The following new capabilities are supported for this release:

Changing the length of a column of type VARCHAR2, VARCHAR, CHAR, NCHAR, NVCHAR, or NVCHAR2
Changing the scale or precision of a column of type NUMBER
Adding, dropping, enabling, or disabling a unique constraint

For information about DDL operations related to version-enabled tables, see Section 1.8.

Unique Constraint Support

Tables with several kinds of unique constraints and indexes on them are supported for version-enabled tables, as explained in Section 1.9.2.

The following metadata views (described in Chapter 5) have been added to contain information relating to Workspace Manger support for unique constraints:

Nested Table Column Support

Tables with nested table columns can now be version-enabled if you set the new ALLOW_NESTED_TABLE_COLUMNS system parameter to ON, as explained in Table 1-4 in Section 1.5.

Virtual Private Database (VPD) Support

You can use Workspace Manager with the Oracle Virtual Private Database (VPD) technology, subject to the considerations described in Section 1.11.

Spatial Topology Support

Special techniques have been implemented for using Workspace Manager with tables in Oracle Spatial topologies, as explained in Section 1.14.

The Add_Topo_Geometry_Layer and Delete_Topo_Geometry_Layer were added to the DBMS_WM package (documented in Chapter 4) to add and delete a topology geometry layer in a version-enabled topology. These procedures have the same format and meaning as the SDO_TOPO.ADD_TOPO_GEOMETRY and SDO_TOPO.DELETE_TOPO_GEOMETRY procedures, documented in Oracle Spatial Topology and Network Data Models; however, you must use the DBMS_WM procedures with topology geometry layers in a version-enabled topology.

History Management Support

Workspace Manager implements history management changes that are especially of interest if you want to perform an upgrade or downgrade operation. For release 10.1, Workspace Manager uses the TIMESTAMP WITH TIME ZONE type with history data, whereas in previous releases it used the DATE type. For more information, see Section B.3.

In addition the USE_TIMESTAMP_TYPE_FOR_HISTORY system parameter lets you specify whether or not to use the TIMESTAMP WITH TIME ZONE or the DATE type for CREATETIME and RETIRETIME columns. The system parameters are described in Section 1.5

New Lock Modes: Workspace-Exclusive and Version-Exclusive

Workspace-exclusive (WE) locks and version-exclusive (VE) locks are new lock types. They are forms of exclusive locking that control which users can and cannot change data values, but (unlike exclusive locking) they do not prevent conflicts from occurring. For more information, see Section 1.3.

New Parameter for Compressing Workspaces

The new remove_latest_deleted_rows parameter for the CompressWorkspace and CompressWorkspaceTree procedures (documented in Chapter 4) lets you control whether or not LATEST deleted rows are preserved.

Renaming a Constraint or Index

To rename a constraint or index on a version-enabled table, you can or must (depending on the length of the constraint or index name) use the AlterVersionedTable procedure (documented in Chapter 4).

New Parameter for RefreshWorkspace

The new copy_data parameter for the RefreshWorkspace procedure (documented in Chapter 4) lets you control the amount of data copied to the child workspace.

Workspace Manager Metadata Placement

You can use the new Move_Proc procedure to move the Workspace Manager metadata to a different tablespace. If you want to get an idea of space requirements before you move the metadata, you can use the GetWMMetadataSpace function to find out how much space is currently used for the Workspace Manager metadata.

Using Default Values for Nulls

You can use the new UseDefaultValuesForNulls procedure (described in Chapter 4) to determine whether or not Workspace Manager, for the current session, uses the default value for a column when the user specifies a null value for the column in an insert operation on a version-enabled table.

DBA_WM_SYS_PRIVS View

The DBA_WM_SYS_PRIVS metadata view (described in Section 5.17) contains information about all users that have Workspace Manager system-level privileges (that is privilege names containing _ANY_WORKSPACE).

Column Name Changes in xxx_HIST Views

The names of the Workspace Manager-specific columns in the history views (described in Section 5.43) were changed: all these names now start with WM_, and some other spelling changes were made. However, the order of the columns and their meanings were not changed.

Sample Schema Example

An example using the OE.WAREHOUSES table from the Oracle sample schemas was added. See Section 1.16.2.