CompressWorkspace Procedure

This procedure deletes explicit savepoints in a workspace and minimizes the Workspace Manager metadata structures for the workspace.

Syntax

DBMS_WM.CompressWorkspace(
   workspace                      IN VARCHAR2
   [, compress_view_wo_overwrite  IN BOOLEAN DEFAULT FALSE]
   [, firstSP                     IN VARCHAR2 DEFAULT NULL
   [, secondSP                    IN VARCHAR2 DEFAULT NULL] ]
   [, auto_commit                 IN BOOLEAN DEFAULT TRUE]);

or

DBMS_WM.CompressWorkspace(
   workspace                      IN VARCHAR2
   [, firstSP                     IN VARCHAR2 DEFAULT NULL
   [, secondSP                    IN VARCHAR2 DEFAULT NULL] ]
   [, auto_commit                 IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 70-6 CompressWorkspace Procedure Parameters  

Parameter	Description
workspace	Name of the workspace. The name is case sensitive.
compress_ view_wo_ overwrite	A boolean value (TRUE or FALSE). TRUE causes history information between the affected savepoints to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled. FALSE (the default) causes history information (between the affected savepoints) for a table not to be deleted if VIEW_WO_OVERWRITE was specified when versioning was enabled. (If VIEW_WO_OVERWRITE was not specified for a table, history information for the table is deleted regardless of the parameter value.)
firstSP	First explicit savepoint. Savepoint names are case sensitive. If only workspace and firstSP are specified, all explicit savepoints between workspace creation and firstSP (but not including firstSP) are deleted. If workspace, firstSP, and secondSP are specified, all explicit savepoints from firstSP (and including firstSP if it is an explicit savepoint) to secondSP (but not including secondSP) are deleted. If only workspace is specified (no savepoints), all explicit savepoints in the workspace are deleted.
secondSP	Second explicit savepoint. All explicit savepoints from firstSP (and including firstSP if it is an explicit savepoint) to secondSP (but not including secondSP) are deleted. Savepoint names are case sensitive.
auto_ commit	A boolean value (TRUE or FALSE). TRUE (the default) causes the operation to be executed as an autonomous regular transaction that will be committed when it finishes. FALSE causes the operation to be executed as part of the caller's open regular transaction (if one exists). If there is no open regular transaction, the operation is executed in a new regular transaction. In either case, the caller is responsible for committing the transaction.

Usage Notes

You can compress a workspace when the explicit savepoints (all or some of them) in the workspace are no longer needed. The compression operation is useful for the following reasons:

• You can reuse savepoint names after they are deleted. (You cannot create a savepoint that has the same name as an existing savepoint.)

• Runtime performance for Workspace Manager operations is improved.

• Less disk storage is used for Workspace Manager structures.

While this procedure is executing, the current workspace is frozen in READ_ONLY mode.

A workspace cannot be compressed if there are any sessions with an open regular transaction, or if any user has executed a GotoDate Procedure operation or a GotoSavepoint Procedure operation specifying a savepoint in the workspace.

For information about VIEW_WO_OVERWRITE and other history options, see the information about the EnableVersioning Procedure.

An exception is raised if the user does not have the privilege to access and merge changes in workspace.

To compress a workspace and all its descendant workspaces, use the CompressWorkspaceTree Procedure.

Examples

The following example compresses NEWWORKSPACE.

EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE');

The following example compresses NEWWORKSPACE, deleting all explicit savepoints between the creation of the workspace and the savepoint SP1.

EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE', 'SP1');

The following example compresses NEWWORKSPACE, deleting the explicit savepoint SP1 and all explicit savepoints up to but not including SP2.

EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE', 'SP1', 'SP2');

The following example compresses B_focus_1, accepts the default values for the firstSP and secondSP parameters (that is, deletes all explicit savepoints), and specifies FALSE for the auto_commit parameter.

EXECUTE DBMS_WM.CompressWorkspace ('B_focus_1', NULL, NULL, FALSE);