What's New in Workspace Manager?

This section describes new and changed Workspace Manager features for Oracle Database Release 11.

Release 11.2 Changes

The following are new and changed features for Oracle Database 11g Release 2 (11.2).

New DBMS_WM Subprograms

The following subprograms have been added to the DBMS_WM PL/SQL package, which is documented in Chapter 4:

  • PurgeTable removes rows (all rows, or as limited by any combination of several parameters) from a version-enabled table, and optionally inserts them into an archive table.

  • RenameSavepoint renames a savepoint in a specified workspace.

  • RenameWorkspace renames a workspace.

Expanded Trigger Support for Version-Enabled Tables

The following enhancements have been made to the support for triggers on version-enabled tables:

  • Before-update and after-update triggers for specific columns are now supported.

  • Triggers specifying multiple DML operations are now supported. For example, you can specify the following:

    CREATE OR REPLACE TRIGGER user2.trig1 AFTER INSERT OR UPDATE OR DELETE ...
    

    In the previous release, you needed to create a separate trigger for each type of DML operation.

For information about using triggers on version-enabled tables, see Section 1.10.

Release 11.1 Changes

The following are new and changed features for Oracle Database 11g Release 1 (11.1).

Note:

The title of this book has been changed, effective with this release. The previous title was Oracle Database Application Developer's Guide - Workspace Manager.

New Workspace Manager System Parameters

The following are new Workspace Manager system parameters for this release:

  • ADD_UNIQUE_COLUMN_TO_HISTORY_VIEW

  • COMPRESS_PARENT_AFTER_REMOVE

  • KEEP_REMOVED_WORKSPACES_INFO

  • ROW_LEVEL_LOCKING

  • TARGET_PGA_MEMORY

  • USE_SCALAR_TYPES_FOR_VALIDTIME

The Workspace Manager system parameters are documented in Section 1.5.

New Options for the AlterVersionedTable Procedure

The following new options are available for the alter_option parameter of the AlterVersionedTable procedure:

  • REBUILD_INDEX, which has the following related new parameter_options parameter keywords: index_owner, index_name, and reverse and noreverse

  • USE_SCALAR_TYPES_FOR_VALIDTIME and USE_WM_PERIOD_FOR_VALIDTIME (only one of which can be specified in each call to the procedure)

The AlterVersionedTable procedure is documented in Chapter 4.

New Parameter for the EnableVersioning Procedure

The validTimeRange parameter (WM_PERIOD DEFAULT NULL) has been added for the EnableVersioning procedure. With this parameter, if you enable valid time support when you version-enable a table, you can specify an initial valid time range.

The EnableVersioning procedure is documented in Chapter 4.

New Views for Removed Workspaces

The new ALL_REMOVED_WORKSPACES and USER_REMOVED_WORKSPACES views contain information about workspaces that have been removed during a RemoveWorkspace operation or a MergeWorkspace operation in which the remove_workspace parameter value was true, and while the value of the Workspace Manager system parameter KEEP_REMOVED_WORKSPACES_INFO was ON.

These views are documented in Chapter 5.

Merging Multiple Tables

You can use the MergeTable procedure to apply changes to multiple tables (all rows or as specified in the WHERE clause) in a workspace to its parent workspace. To specify multiple tables, specify them in the table_id parameter and separate the names with commas (for example, 'table1, table2'). In previous releases, you could specify only a single table name.

The MergeTable procedure is documented in Chapter 4.

Workspace ID Column Added to Static Data Dictionary Views

A numeric WORKSPACE_ID column has been added to the ALL_WORKSPACES and USER_WORKSPACES views.

These views are documented in Chapter 5.

User-Defined Hints

You can specify user-defined hints, which modify (and thus override) default optimizer hints, with the goal of improving the performance of SQL statements executed by the DBMS_WM package on a specified version-enabled table or all version-enabled tables. To add a user-defined hint, use the new AddUserDefinedHint procedure; to remove a user-defined hint, use the new RemoveUserDefinedHint procedure.

These procedures are documented in Chapter 4.

Adding, Merging, or Splitting Table Partitions

You can add, merge, and split table partitions in a version-enabled table by using the alter_option parameter to the AlterVersionedTable procedure, which is documented in Chapter 4.

Null Foreign Key Constraints

SET NULL foreign key constraints are now supported, and any such constraint is reflected in the ALL_WM_RIC_INFO metadata view with a row for which the DELETE_RULE column is set to N. (The value in this column does not affect behavior in any way; it only displays metadata about the table.) The ALL_WM_RIC_INFO metadata view is described in Section 5.11.

Oracle Label Security Policies and Version-Enabled Tables

If you need to perform DDL operations on a version-enabled table in an Oracle Label Security (OLS) environment, you can use the apply_table_policy, remove_table_policy, enable_table_policy, and disable_table_policy procedures of the SA_POLICY_ADMIN package on the skeleton (_LTS) table, and the changes will be transferred to the version-enabled table.