Summary of DBMS_WM Subprograms

Table 80-1 DBMS_WM Subprograms  

AlterSavepoint Procedure

Modifies the description of a savepoint.

Syntax

DBMS_WM.AlterSavepoint(
   workspace      IN VARCHAR2,
   sp_name        IN VARCHAR2,
   sp_description IN VARCHAR2);

Parameters

Table 80-2 AlterSavepoint Procedure Parameters  

workspace

sp_name

sp_description

Usage Notes

To see the current description of the savepoint, examine the DESCRIPTION column value for the savepoint in the ALL_WORKSPACE_SAVEPOINTS metadata view, which is described in Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if the user is not the workspace owner or savepoint owner or does not have the WM_ADMIN_ROLE role.

Examples

The following example modifies the description of savepoint SP1 in the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.AlterSavepoint (`NEWWORKSPACE', 'SP1', 'First set of changes for 
scenario');

AlterWorkspace Procedure

Modifies the description of a workspace.

Syntax

DBMS_WM.AlterWorkspace(
   workspace              IN VARCHAR2,
   workspace_description  IN VARCHAR2);

Parameters

Table 80-3 AlterWorkspace Procedure Parameters  

workspace

workspace_description

Usage Notes

To see the current description of the workspace, examine the DESCRIPTION column value for the savepoint in the ALL_WORKSPACES metadata view, which is described in Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if the user is not the workspace owner or does not have the WM_ADMIN_ROLE role.

Examples

The following example modifies the description of the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.AlterWorkspace (`NEWWORKSPACE', 'Testing proposed scenario B');

BeginDDL Procedure

Starts a DDL (data definition language) session for a specified table.

Syntax

DBMS_WM.BeginDDL(
   table_name  IN VARCHAR2);

Parameters

Table 80-4 BeginDDL Procedure Parameters  

table_name

Usage Notes

This procedure starts a DDL session, and it creates a special table whose name is the same as table_name but with _LTS added to the table name. After calling this procedure, you can perform one or more DDL operations on the table or any indexes or triggers that are based on the table, and then call either the CommitDDL Procedure or RollbackDDL Procedure.

In addition to creating the special <table-name>_LTS table, the procedure creates other objects:

• The <table-name>_LTS table has the same triggers, columns, and indexes as the <table-name> table.
• For each parent table with which the <table-name> table has a referential integrity constraint, the same constraint is defined for the <table-name>_LTS table.
• Triggers, columns, and referential integrity constraints on the <table-name>_LTS table have the same names as the corresponding ones on the <table-name> table.
• For each index on the <table-name> table, the corresponding index on the <table-name>_LTS table has a name in the form <index-name>_LTS.
• The primary key constraint on the <table-name>_LTS table has a name in the form <primary-key>_LTS.

For detailed information about performing DDL operations related to version-enabled tables and about DDL operations on version-enabled tables in an Oracle replication environment, see Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if one or more of the following apply:

• table_name does not exist or is not version-enabled.
• The user does not have the CREATE TABLE privilege.
• An open DDL session exists for table_name. (That is, the BeginDDL procedure has already been called specifying this table, and the CommitDDL Procedure or RollbackDDL Procedure has not been called specifying this table.)

Examples

The following example begins a DDL session, adds a column named COMMENTS to the COLA_MARKETING_BUDGET table by using the special table named COLA_MARKETING_BUDGET_LTS, and ends the DDL session by committing the change.

EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET');
ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100));
EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');

BeginResolve Procedure

Starts a conflict resolution session.

Syntax

DBMS_WM.BeginResolve(
   workspace  IN VARCHAR2);

Parameters

Table 80-5 BeginResolve Procedure Parameters  

workspace

Usage Notes

This procedure starts a conflict resolution session. While this procedure is executing, the workspace is frozen in 1WRITER mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

After calling this procedure, you can execute the ResolveConflicts Procedure as needed for various tables that have conflicts, and then call either the CommitResolve Procedure or RollbackResolve Procedure. For more information about conflict resolution, see Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if one or more of the following apply:

• There are one or more open database transactions in workspace.
• The user executing the BeginResolve Procedure does not have the privilege to access workspace and its parent workspace.

Examples

The following example starts a conflict resolution session in Workspace1.

EXECUTE  DBMS_WM.BeginResolve ('Workspace1');

CommitDDL Procedure

Commits DDL (data definition language) changes made during a DDL session for a specified table, and ends the DDL session.

Syntax

DBMS_WM.CommitDDL(
   table_name           IN VARCHAR2
   [, ignore_last_error IN BOOLEAN DEFAULT FALSE]);

Parameters

Table 80-6 CommitDDL Procedure Parameters  

table_name

ignore_
last_error

Usage Notes

This procedure commits changes that were made to a version-enabled table and to any indexes, triggers, and referential integrity constraints based on the version-enabled table during a DDL session. It also deletes the special <table-name>_LTS table that had been created by the BeginDDL Procedure.

For detailed information about performing DDL operations related to version-enabled tables and about DDL operations on version-enabled tables in an Oracle replication environment, see Oracle9i Application Developer's Guide - Workspace Manager.

If a call to the CommitDDL procedure fails, the table is left in an inconsistent state. If this occurs, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. For example, the CommitDDL procedure might have failed because the tablespace was not large enough to add a column. Fix the cause of the error, and then call the CommitDDL procedure again with the default ignore_last_error parameter value of FALSE. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the CommitDDL procedure with the ignore_last_error parameter value of TRUE. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.

An exception is raised if one or more of the following apply:

• table_name does not exist or is not version-enabled.
• The user does not have the CREATE TABLE privilege.
• An open DDL session does not exist for table_name. (That is, the BeginDDL Procedure has not been called specifying this table, or the CommitDDL Procedure or RollbackDDL Procedure was already called specifying this table.)

Some invalid DDL operations also cause an exception when CommitDDL procedure is called. (See Oracle9i Application Developer's Guide - Workspace Manager for information about DDL operations that are supported.)

Examples

The following example begins a DDL session, adds a column named COMMENTS to the COLA_MARKETING_BUDGET table by using the special table named COLA_MARKETING_BUDGET_LTS, and ends the DDL session by committing the change.

EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET');
ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100));
EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');

CommitResolve Procedure

Ends a conflict resolution session and saves (makes permanent) any changes in the workspace since the BeginResolve Procedure was executed.

Syntax

DBMS_WM.CommitResolve(
   workspace  IN VARCHAR2);

Parameters

Table 80-7 CommitResolve Procedure Parameters  

workspace

Usage Notes

This procedure ends the current conflict resolution session (started by the BeginResolve Procedure), and saves all changes in the workspace since the start of the conflict resolution session. Contrast this procedure with the RollbackResolve Procedure, which discards all changes.

For more information about conflict resolution, see Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if one or more of the following apply:

• There are one or more open database transactions in workspace.
• The procedure was called by a user that does not have the WM_ADMIN_ROLE role or that did not execute the BeginResolve Procedure on workspace.

Examples

The following example ends the conflict resolution session in Workspace1 and saves all changes.

EXECUTE  DBMS_WM.CommitResolve ('Workspace1');

CompressWorkspace Procedure

Deletes removable savepoints in a workspace and minimizes the Workspace Manager metadata structures for the workspace. (Removable savepoints are explained in Oracle9i Application Developer's Guide - Workspace Manager.)

Syntax

DBMS_WM.CompressWorkspace(
   workspace                   IN VARCHAR2,
   compress_view_wo_overwrite  IN BOOLEAN
   [, 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 80-8 CompressWorkspace Procedure Parameters  

workspace

compress_
view_wo_
overwrite

firstSP

secondSP

auto_
commit

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 NO_ACCESS mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

A workspace cannot be compressed if there are any sessions in the workspace (except for the LIVE workspace), or if any user has executed a GotoDate Procedure operation or a GotoSavepoint Procedure operation specifying a savepoint in the workspace.

If the procedure format without the compress_view_wo_overwrite parameter is used, a value of FALSE is assumed for the parameter.

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', auto_commit => FALSE);

CompressWorkspaceTree Procedure

Deletes removable savepoints in a workspace and all its descendant workspaces. (Removable savepoints are explained in Oracle9i Application Developer's Guide - Workspace Manager.) It also minimizes the Workspace Manager metadata structures for the affected workspaces, and eliminates any redundant data that might arise from the deletion of the savepoints.

Syntax

DBMS_WM.CompressWorkspaceTree(
   workspace                      IN VARCHAR2
   [, compress_view_wo_overwrite  IN BOOLEAN DEFAULT FALSE]
   [, auto_commit                 IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 80-9 CompressWorkspaceTree Procedure Parameters  

workspace

compress_
view_wo_
overwrite

auto_
commit

Usage Notes

You can compress a workspace and all its descendant workspaces when the explicit savepoints in the affected workspaces are no longer needed (for example, if you will not need to go to or roll back to any of these savepoints). For an explanation of database workspace hierarchy, see Oracle9i Application Developer's Guide - Workspace Manager.

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 NO_ACCESS mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

A workspace cannot be compressed if there are any sessions in the workspace (except for the LIVE workspace), or if any user has executed a GotoDate Procedure operation or a GotoSavepoint Procedure operation specifying a savepoint in the workspace.

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

If the CompressWorkspaceTree operation fails in any affected workspace, the entire operation is rolled back, and no workspaces are compressed.

To compress a single workspace (deleting all explicit savepoints or just some of them), use the CompressWorkspace Procedure.

Examples

The following example compresses NEWWORKSPACE and all its descendant workspaces.

EXECUTE DBMS_WM.CompressWorkspaceTree (`NEWWORKSPACE');

The following example compresses NEWWORKSPACE and all its descendant workspaces, accepts the default value for the compress_view_wo_overwrite parameter, and specifies FALSE for the auto_commit parameter.

EXECUTE DBMS_WM.CompressWorkspaceTree ('B_focus_1', auto_commit => FALSE);

CopyForUpdate Procedure

Allows LOB columns (BLOB, CLOB, or NCLOB) in version-enabled tables to be modified. Use this procedure only if a version-enabled table has any LOB columns.

Syntax

DBMS_WM.CopyForUpdate(
   table_name       IN VARCHAR2,
   [, where_clause  IN VARCHAR2 DEFAULT '']);

Parameters

Table 80-10 CopyForUpdate Procedure Parameters  

table_name

where_clause

Usage Notes

This procedure is intended for use only with version-enabled tables containing one or more large object (LOB) columns. The CopyForUpdate procedure must be used because updates performed using the DBMS_LOB package do not fire INSTEAD OF triggers on the versioning views. Workspace Manager creates INSTEAD OF triggers on the versioning views to implement the copy-on-write semantics. (For non-LOB columns, you can directly perform the update operation, and the triggers work.)

Examples

The following example updates the SOURCE_CLOB column of TABLE1 for the document with DOC_ID = 1.

  Declare 
    clob_var 
  Begin
     /* This procedure copies the LOB columns if necessary, that is, 
        if the row with doc_id = 1 has not been versioned in the 
        current version */
     dbms_wm.copyForUpdate('table1', 'doc_id = 1');

     select source_clob into clob_var
     from   table1
     where  doc_id = 1 for update;

     dbms_lob.write(clob_var,<amount>, <offset>, buff);

  End;

CreateSavepoint Procedure

Creates a savepoint for the current version.

Syntax

DBMS_WM.CreateSavepoint(
   workspace       IN VARCHAR2,
   savepoint_name  IN VARCHAR2
   [, description  IN VARCHAR2 DEFAULT NULL]
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 80-11 CreateSavepoint Procedure Parameters  

workspace

savepoint_name

description

auto_commit

Usage Notes

There are no explicit privileges associated with savepoints; any user who can access a workspace can create a savepoint in the workspace.

This procedure can be performed while there are users in the workspace; there can be open database transactions.

While this procedure is executing, the current workspace is frozen in READ_ONLY mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

An exception is raised if one or more of the following apply:

• The user is not in the latest version in the workspace (for example, if the user has called the GotoDate Procedure).
• workspace does not exist.
• savepoint_name already exists.
• The user does not have the privilege to go to the specified workspace.

Examples

The following example creates a savepoint named Savepoint1 in the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.CreateSavepoint ('NEWWORKSPACE', 'Savepoint1');

CreateWorkspace Procedure

Creates a new workspace in the database.

Syntax

DBMS_WM.CreateWorkspace(
   workspace       IN VARCHAR2
   [, description  IN VARCHAR2 DEFAULT NULL]
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

or

DBMS_WM.CreateWorkspace(
   workspace       IN VARCHAR2,
   isrefreshed     IN BOOLEAN
   [, description  IN VARCHAR2 DEFAULT NULL]
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 80-12 CreateWorkspace Procedure Parameters  

workspace

isrefreshed

description

auto_commit

Usage Notes

The new workspace is a child of the current workspace. If the session has not explicitly entered a workspace, it is in the LIVE database workspace, and the new workspace is a child of the LIVE workspace. For an explanation of database workspace hierarchy, see Oracle9i Application Developer's Guide - Workspace Manager.

An implicit savepoint is created in the current version of the current workspace. (The current version does not have to be the latest version in the current workspace.) For an explanation of savepoints (explicit and implicit), see Oracle9i Application Developer's Guide - Workspace Manager.

While this procedure is executing, the current workspace is frozen in READ_ONLY mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

This procedure does not implicitly go to the workspace created. To go to the workspace, use the GotoWorkspace Procedure.

The following rules apply to continually refreshed workspaces (isrefreshed value of TRUE):

• A continually refreshed workspace must be created as a child of the LIVE workspace.
• A continually refreshed workspace must be a leaf workspace (that is, have no child workspaces).
• The session must be on the latest version in order to create a continually refreshed workspace.
• You cannot turn off locking using the SetLockingOFF Procedure or SetWorkspaceLockModeOFF Procedure for a continually refreshed workspace.

An exception is raised if one or more of the following apply:

• workspace already exists.
• The user does not have the privilege to create a workspace.

Examples

The following example creates a workspace named NEWWORKSPACE in the database.

EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE');

DeleteSavepoint Procedure

Deletes a savepoint and associated rows in version-enabled tables.

Syntax

DBMS_WM.DeleteSavepoint(
   workspace                      IN VARCHAR2,
   savepoint_name                 IN VARCHAR2)
   [, compress_view_wo_overwrite  IN BOOLEAN DEFAULT FALSE]
   [, auto_commit                 IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 80-13 DeleteSavepoint Procedure Parameters  

workspace

savepoint_name

compress_view_
wo_overwrite

auto_commit

Usage Notes

You can delete a savepoint when it is no longer needed (for example, you will not need to go to it or roll back to it).

Deleting a savepoint is useful for the following reasons:

• You can reuse a savepoint name after it is 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 NO_ACCESS mode, as explained in Oracle9i Application Developer's Guide - Workspace Manager.

To delete a savepoint, you must have the WM_ADMIN_ROLE role or be the owner of the workspace or the savepoint.

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

An exception is raised if one or more of the following apply:

• One or more sessions are already in workspace (unless the workspace is LIVE).
• workspace does not exist.
• savepoint_name does not exist.
• savepoint_name is not a removable savepoint. (Removable savepoints are explained in Oracle9i Application Developer's Guide - Workspace Manager.)
• The user does not have the privilege to go to the specified workspace.

Examples

The following example deletes a savepoint named Savepoint1 in the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.DeleteSavepoint ('NEWWORKSPACE', 'Savepoint1');

DisableVersioning Procedure

Deletes all support structures that were created to enable the table to support versioned rows.

Syntax

DBMS_WM.DisableVersioning(
   table_name           IN VARCHAR2
   [, force             IN BOOLEAN DEFAULT FALSE]
   [, ignore_last_error IN BOOLEAN DEFAULT FALSE]);

Parameters

Table 80-14 DisableVersioning Procedure Parameters  

table_name

force

ignore_
last_error

Usage Notes

This procedure is used to reverse the effect of the EnableVersioning Procedure. It deletes the Workspace Manager infrastructure (support structures) for versioning of rows, but does not affect any user data in the LIVE workspace. The workspace hierarchy and any savepoints still exist, but all rows are the same as in the LIVE workspace. (If there are multiple versions in the LIVE workspace of a row in the table for which versioning is disabled, only the most recent version of the row is kept.)

If a call to the DisableVersioning procedure fails, the table is left in an inconsistent state. If this occurs, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message), and then call the DisableVersioning procedure again with the default ignore_last_error parameter value of FALSE. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the DisableVersioning procedure with the ignore_last_error parameter value of TRUE. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.

Some causes for the failure of the DisableVersioning procedure include the following:

• The table contains much data in workspaces and the size of the undo tablespace required for the DisableVersioning procedure is not sufficient.
• A compilation error occurred while transferring user-defined triggers from the version-enabled table to the version-disabled table.

The DisableVersioning operation fails if the force value is FALSE and any of the following apply:

• The table is being modified by any user in any workspace other than the LIVE workspace.
• There are versioned rows of the table in any workspace other than the LIVE workspace.

Only the owner of a table or a user with the WM_ADMIN_ROLE role can disable versioning on the table.

Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.

An exception is raised if the table is not version-enabled.

If you want to disable versioning on a table in an Oracle replication environment, see Oracle9i Application Developer's Guide - Workspace Manager for guidelines and other information.

Examples

The following example disables the EMPLOYEE table for versioning.

EXECUTE DBMS_WM.DisableVersioning ('employee');

The following example disables the EMPLOYEE table for versioning and ignores the last error that occurred during the previous call to the DisableVersioning procedure.

EXECUTE DBMS_WM.DisableVersioning ('employee', ignore_last_error => true);

The following example disables the EMPLOYEE, DEPARTMENT, and LOCATION tables (which have multilevel referential integrity constraints) for versioning.

EXECUTE DBMS_WM.DisableVersioning('employee,department,location');

DropReplicationSupport Procedure

Deletes replication support objects that had been created by the GenerateReplicationSupport Procedure.

Syntax

DBMS_WM.DropReplicationSupport();

Parameters

None.

Usage Notes

To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Oracle9i Application Developer's Guide - Workspace Manager. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle9i Replication and Oracle9i Replication Management API Reference.

You must execute this procedure as the replication administrator user at the writer site.

This procedure drops replication support for any version-enabled tables at the nonwriter sites; however, it does not version-disable any version-enabled tables.

Examples

The following example drops replication support that had previously been enabled using the GenerateReplicationSupport Procedure.

DBMS_WM.DropReplicationSupport();

EnableVersioning Procedure

Version-enables a table, creating the necessary structures to enable the table to support multiple versions of rows.

Syntax

DBMS_WM.EnableVersioning(
   table_name  IN VARCHAR2
   [, hist     IN VARCHAR2 DEFAULT 'NONE']);

Parameters

Table 80-15 EnableVersioning Procedure Parameters  

table_name

hist

Usage Notes

The table that is being version-enabled must have a primary key defined.

Only the owner of a table or a user with the WM_ADMIN role can enable versioning on the table.

Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.

Tables owned by SYS cannot be version-enabled.

An exception is raised if one or more of the following apply:

• table_name is already version-enabled.
• table_name contains a list of tables and any of the tables has a referential integrity constraint with a table that is not in the list.

If the table is version-enabled with the VIEW_WO_OVERWRITE hist option specified, this option can later be disabled and re-enabled by calling the SetWoOverwriteOFF Procedure and SetWoOverwriteON Procedures.

The history option enables you to log and audit modifications.

The history option affects the behavior of the GotoDate Procedure. See the Usage Notes for that procedure.

If you want to version-enable a table in an Oracle replication environment, see Oracle9i Application Developer's Guide - Workspace Manager for guidelines and other information.

Current notes and restrictions include the following:

• If you have referential integrity constraints on version-enabled tables, note the considerations and restrictions in Oracle9i Application Developer's Guide - Workspace Manager.
• If you have triggers defined on version-enabled tables, note the considerations and restrictions in Oracle9i Application Developer's Guide - Workspace Manager.
• Constraints and privileges defined on the table are carried over to the version-enabled table.
• DDL operations on version-enabled tables are subject to the procedures and restrictions described in Oracle9i Application Developer's Guide - Workspace Manager.
• Index-organized tables cannot be version-enabled.
• Object tables cannot be version-enabled.
• A table with one or more columns of LONG data type cannot be version-enabled.
• A table with one or more nested table columns cannot be version-enabled.

Examples

The following example enables versioning on the EMPLOYEE table.

EXECUTE DBMS_WM.EnableVersioning('employee');

The following example enables versioning on the EMPLOYEE, DEPARTMENT, and LOCATION tables, which have multilevel referential integrity constraints.

EXECUTE DBMS_WM.EnableVersioning('employee,department,location');

FreezeWorkspace Procedure

Restricts access to a workspace and the ability of users to make changes in the workspace.

Syntax

DBMS_WM.FreezeWorkspace(
   workspace        IN VARCHAR2
   [, freezemode    IN VARCHAR2 DEFAULT 'NO_ACCESS']
   [, freezewriter  IN VARCHAR2 DEFAULT NULL] 
   [, force         IN BOOLEAN DEFAULT FALSE]);

or

DBMS_WM.FreezeWorkspace(
   workspace        IN VARCHAR2,
   session_duration IN BOOLEAN
   [, freezemode    IN VARCHAR2 DEFAULT 'NO_ACCESS']
   [, freezewriter  IN VARCHAR2 DEFAULT NULL] 
   [, force         IN BOOLEAN DEFAULT FALSE]);

Parameters

Table 80-16 FreezeWorkspace Procedure Parameters  

workspace

session_
duration

freezemode

freezewriter

force

Usage Notes

If you specify the procedure syntax that does not include the session_duration parameter, it is equivalent to specifying FALSE for that parameter: that is, the workspace is not unfrozen when the session that called the FreezeWorkspace procedure disconnects from the database.

The operation fails if one or more of the following apply:

• workspace is already frozen (unless force is TRUE).
• Any sessions are in workspace and freezemode is NO_ACCESS (specified or defaulted).
• session_duration is FALSE and freezemode is 1WRITER_SESSION.

If freezemode is READ_ONLY or 1WRITER, the workspace cannot be frozen if there is an active database transaction.

You can freeze a workspace only if one or more of the following apply:

• You are the owner of the specified workspace.
• You have the WM_ADMIN_ROLE, the FREEZE_ANY_WORKSPACE privilege, or the FREEZE_WORKSPACE privilege for the specified workspace.

The LIVE workspace can be frozen only if freezemode is READ_ONLY or 1WRITER.

To reverse the effect of FreezeWorkspace, use the UnfreezeWorkspace Procedure.

Examples

The following example freezes the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.FreezeWorkspace ('NEWWORKSPACE');

GenerateReplicationSupport Procedure

Creates necessary structures for multimaster replication of Workspace Manager objects, and starts the master activity for the newly created master group.

Syntax

DBMS_WM.GenerateReplicationSupport(
   mastersites          IN VARCHAR2,
   groupname            IN VARCHAR2
   [, groupdescription  IN VARCHAR2 DEFAULT 'Replication Group for OWM']);

Parameters

Table 80-17 GenerateReplicationSupport Procedure Parameters  

mastersites

groupname

groupdescription

Usage Notes

To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Oracle9i Application Developer's Guide - Workspace Manager. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle9i Replication and Oracle9i Replication Management API Reference.

You must execute this procedure as the replication administrator user at the writer site.

Before executing this procedure, ensure that the following are true:

• There are no workspaces, savepoints, or version-enabled tables on any of the remote sites specified in the mastersites list
• All the remote sites and the local site have the same version of Workspace Manager installed. You can check the Workspace Manager version number in the WM_INSTALLATION metadata view.
• If there are version-enabled tables on the local site, these tables must exist and must not be version-enabled on each of the remote sites.

This procedure performs the following operations:

• Verifies that the local site and all the sites specified in the mastersites list are running the same version of Workspace Manager.
• Verifies that there are no workspaces, savepoints, or version-enabled tables on any of the remote sites specified in the mastersites list.
• Creates a master group, having the name specified in the groupname parameter, with the local site as the master definition site and the writer site.
• Adds the Workspace Manager metadata tables to this group.
• Disables Workspace Manager operations at all the nonwriter sites (the remote sites specified in the mastersites list).
• If there are any version-enabled tables at the local site, version-enables these tables at each of the remote sites specified in the mastersites list and sets them up for replication.
• Starts the master activity for the newly created master group.

To drop replication support for the Workspace Manager environment, use the DropReplicationSupport Procedure.

Examples

The following example generates replication support for the Workspace Manager environment at a hypothetical company.

DBMS_WM.GenerateReplicationSupport(
    mastersites       =>  `BACKUP-SITE1.ACME.COM, BACKUP-SITE2.ACME.COM');
    groupname         =>  `OWM-GROUP',
    groupdescription  =>  `OWM Replication group for Acme Corp.');

GetConflictWorkspace Function

Returns the name of the workspace on which the session has performed the SetConflictWorkspace Procedure.

Format

DBMS_WM.GetConflictWorkspace() RETURN VARCHAR2;

Parameters

None.

Usage Notes

If the SetConflictWorkspace Procedure has not been executed, the name of the current workspace is returned.

Examples

The following example displays the name of the workspace on which the session has performed the SetConflictWorkspace Procedure.

SELECT DBMS_WM.GetConflictWorkspace FROM DUAL;

GETCONFLICTWORKSPACE                                                            
-----------------------------------------------------------------------------
B_focus_2  

GetDiffVersions Function

Returns the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions Procedure operation.

Format

DB