Skip Headers

Oracle9i Application Developer's Guide - Workspace Manager
Release 2 (9.2)

Part Number A96628-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

2
Procedures: Reference

Workspace Manager includes procedures that perform the available features of the product. This chapter provides reference information on each procedure.


Note:

Most Workspace Manager interfaces are procedures, but a few are functions. (A function returns a value; a procedure does not return a value.)

Most functions have names starting with Get (such as GetConflictWorkspace and GetWorkspace).

In this guide, the term procedures is often used to refer generally to both procedures and functions.


The procedures are presented in alphabetical order. For a brief description of procedures according to their logical groupings, see Section 1.10.

Errors (exceptions) that can occur with Workspace Manager procedures are documented in Appendix D, including the cause and suggested user action for each error.

Syntax notes:


AlterSavepoint

Purpose

Modifies the description of a savepoint.

Syntax

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

Parameters

Table 2-1 AlterSavepoint Procedure Parameters  
Parameter Description
workspace

Name of the workspace in which the savepoint was created. The name is case sensitive.

sp_name

Name of the savepoint. The name is case sensitive.

sp_description

Description of the savepoint.

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 Section 3.9.

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

Purpose

Modifies the description of a workspace.

Syntax

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

Parameters

Table 2-2 AlterWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

workspace_description

Description of the workspace.

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 Section 3.10.

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

Purpose

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

Syntax

DBMS_WM.BeginDDL(
   table_name  IN VARCHAR2);

Parameters

Table 2-3 BeginDDL Procedure Parameters  
Parameter Description
table_name

Name of the version-enabled table. The name is not case sensitive.

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 or RollbackDDL procedure.

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

For detailed information about performing DDL operations related to version-enabled tables, see Section 1.6; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.

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

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

Purpose

Starts a conflict resolution session.

Syntax

DBMS_WM.BeginResolve(
   workspace  IN VARCHAR2);

Parameters

Table 2-4 BeginResolve Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

This procedure starts a conflict resolution session. While this procedure is executing, the workspace is frozen in 1WRITER mode, as explained in Section 1.1.5.

After calling this procedure, you can execute the ResolveConflicts procedure as needed for various tables that have conflicts, and then call either the CommitResolve or RollbackResolve procedure. For more information about conflict resolution, see Section 1.1.4.

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

Examples

The following example starts a conflict resolution session in Workspace1.

EXECUTE  DBMS_WM.BeginResolve ('Workspace1');

CommitDDL

Purpose

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 2-5 CommitDDL Procedure Parameters  
Parameter Description
table_name

Name of the version-enabled table. The name is not case sensitive.

ignore_
last_error

A Boolean value (TRUE or FALSE).

TRUE ignores the last error, if any, that occurred during the previous call to the CommitDDL procedure. Information about the last error is stored in the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views, which are described in Chapter 3. (See the Usage Notes for more information.)

FALSE (the default) does not ignore the last error, if any, that occurred during the previous call to the CommitDDL procedure.

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, see Section 1.6; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.

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:

Some invalid DDL operations also cause an exception when CommitDDL procedure is called. (See Section 1.6 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

Purpose

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 2-6 CommitResolve Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

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 Section 1.1.4.

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

Examples

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

EXECUTE  DBMS_WM.CommitResolve ('Workspace1');

CompressWorkspace

Purpose

Deletes removable savepoints in a workspace and minimizes the Workspace Manager metadata structures for the workspace. (Removable savepoints are explained in Section 1.1.2.)

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 2-7 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 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.) FALSE is assumed if the procedure format without this parameter is used.

firstSP

First savepoint. Savepoint names are case sensitive.

If only workspace and firstSP are specified, all removable savepoints between workspace creation and firstSP (but not including firstSP) are deleted.

If workspace, firstSP, and secondSP are specified, all removable savepoints from firstSP (and including firstSP if it is a removable savepoint) to secondSP (but not including secondSP) are deleted.

If only workspace is specified (no savepoints), all removable savepoints in the workspace are deleted.

secondSP

Second savepoint. All removable savepoints from firstSP (and including firstSP if it is a removable savepoint) to secondSP (but not including secondSP) are deleted.

However, if secondSP is LATEST, all removable savepoints from firstSP (and including firstSP if it is a removable savepoint) to the end of the workspace 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 database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

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:

While this procedure is executing, the current workspace is frozen in NO_ACCESS mode, as explained in Section 1.1.5.

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 operation or a GotoSavepoint 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

Purpose

Deletes removable savepoints in a workspace and all its descendant workspaces. (Removable savepoints are explained in Section 1.1.2.) 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 2-8 CompressWorkspaceTree 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 to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled.

FALSE (the default) causes history information 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.)

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

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 example, in the hierarchy shown in Figure 1-1 in Section 1.1.1, a CompressWorkspaceTree operation specifying Workspace1 compresses Workspace1, Workspace2, and Workspace3. (For an explanation of database workspace hierarchy, see Section 1.1.1.)

The compression operation is useful for the following reasons:

While this procedure is executing, the current workspace is frozen in NO_ACCESS mode, as explained in Section 1.1.5.

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 operation or a GotoSavepoint 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

Purpose

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 2-9 CopyForUpdate Procedure Parameters  
Parameter Description
table_name

Name of the table containing one or more LOB columns. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows affected. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows in table_name are affected.

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

Purpose

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 2-10 CreateSavepoint Procedure Parameters  
Parameter Description
workspace

Name of the workspace in which to create the savepoint. The name is case sensitive.

savepoint_name

Name of the savepoint to be created. The name is case sensitive.

description

Description of the savepoint to be created.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

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 Section 1.1.5.

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

Examples

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

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

CreateWorkspace

Purpose

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 2-11 CreateWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive, and it must be unique (no other workspace of the same name).

isrefreshed

A Boolean value (TRUE or FALSE).

TRUE causes the workspace to be continually refreshed. In a continually refreshed workspace, changes made in the parent workspace are automatically applied to the workspace after a merge or rollback operation in the parent workspace. That is, you do not need to call the RefreshWorkspace procedure to apply the changes. See the Usage Notes for more information about continually refreshed workspaces.

FALSE causes the workspace not to be continually refreshed. To refresh the workspace, you must call the RefreshWorkspace procedure.

If you use the syntax without the isrefreshed parameter, the workspace is not continually refreshed.

description

Description of the workspace.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

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 Section 1.1.1.

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 Section 1.1.2.

While this procedure is executing, the current workspace is frozen in READ_ONLY mode, as explained in Section 1.1.5.

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):

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

Examples

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

EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE');

DeleteSavepoint

Purpose

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 2-12 DeleteSavepoint Procedure Parameters  
Parameter Description
workspace

Name of the workspace in which the savepoint was created. The name is case sensitive.

savepoint_name

Name of the savepoint to be deleted. The name is case sensitive.

compress_view_
wo_overwrite

A Boolean value (TRUE or FALSE).

TRUE causes history information to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled.

FALSE (the default) causes history information 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.)

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

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:

While this procedure is executing, the current workspace is frozen in NO_ACCESS mode, as explained in Section 1.1.5.

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 operation or a GotoSavepoint operation specifying a savepoint in the workspace.

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

Examples

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

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

DisableVersioning

Purpose

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 2-13 DisableVersioning Procedure Parameters  
Parameter Description
table_name

Name of the table, or a comma-delimited list of names of tables related by multilevel referential integrity constraints. (Multilevel referential integrity constraints are explained in Section 1.7.) Table names are not case sensitive.

force

A Boolean value (TRUE or FALSE).

TRUE forces all data in workspaces other than LIVE to be discarded before versioning is disabled.

FALSE (the default) prevents versioning from being disabled if table_name was modified in any workspace other than LIVE and if the workspace that modified table_name still exists.

ignore_
last_error

A Boolean value (TRUE or FALSE).

TRUE ignores the last error, if any, that occurred during the previous call to the DisableVersioning procedure. Information about the last error is stored in the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views, which are described in Chapter 3. (See the Usage Notes for more information.)

FALSE (the default) does not ignore the last error, if any, that occurred during the previous call to the DisableVersioning procedure.

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 DisableVersioning operation fails if the force value is FALSE and any of the following apply:

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 Section C.2 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

Purpose

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 Appendix C. 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

Purpose

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 2-14 EnableVersioning Procedure Parameters  
Parameter Description
table_name

Name of the table, or a comma-delimited list of names of tables related by multilevel referential integrity constraints. (Multilevel referential integrity constraints are explained in Section 1.7.) The length of a table name must not exceed 25 characters. The name is not case sensitive.

hist

History option, for tracking modifications to table_name. Must be one of the following values:

NONE: No modifications to the table are tracked. (This is the default.)

VIEW_W_OVERWRITE: The with overwrite (W_OVERWRITE) option. A view named <table_name>_HIST (described in Section 3.28) is created to contain history information, but it will show only the most recent modifications to the same version of the table. A history of modifications to the version is not maintained; that is, subsequent changes to a row in the same version overwrite earlier changes. (The CREATETIME column of the <table_name>_HIST view contains only the time of the most recent update.)

VIEW_WO_OVERWRITE: The without overwrite (WO_OVERWRITE) option. A view named <table_name>_HIST (described in Section 3.28) is created to contain history information, and it will show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

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:

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 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 Section C.2 for guidelines and other information.

Current notes and restrictions include the following:

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

Purpose

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 2-15 FreezeWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

session_
duration

A Boolean value (TRUE or FALSE).

TRUE causes the workspace to be unfrozen when the session that called the FreezeWorkspace procedure disconnects from the database. This value is valid for all freeze modes.

FALSE causes the workspace not to be unfrozen when the session that called the FreezeWorkspace procedure disconnects from the database.

freezemode

Mode for the frozen workspace. Must be one of the following values:

NO_ACCESS: No sessions are allowed in the workspace. (This is the default.)

READ_ONLY: Sessions are allowed in the workspace, but no write operations (insert, update, delete) are allowed.

1WRITER: Sessions are allowed in the workspace, but only one user (see the freezewriter parameter) is allowed to perform write operations (insert, update, delete).

1WRITER_SESSION: Sessions are allowed in the workspace, but only the database session (as opposed to the database user) that called the FreezeWorkspace procedure is allowed to perform write operations (insert, update, delete). The workspace is unfrozen after the session that called the FreezeWorkspace procedure disconnects from the database.

WM_ONLY: Only Workspace Manager operations are permitted. No sessions can directly modify data values or perform queries involving table data; however, child workspaces can be merged into the workspace, and savepoints can be created in the workspace.

freezewriter

The user that is allowed to make changes in the workspace. Can be specified only if freezemode is 1WRITER. The default is USER (the current user).

force

A Boolean value (TRUE or FALSE).

TRUE forces the workspace to be frozen even if it is already frozen. For example, this value lets you freeze the workspace with a different freezemode parameter value without having first to call the UnfreezeWorkspace procedure.

FALSE (the default) prevents the workspace from being frozen if it is already frozen.

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:

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:

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

Purpose

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 2-16 GenerateReplicationSupport Procedure Parameters  
Parameter Description
mastersites

Comma-delimited list of nonwriter site names (database links) to be added to the Workspace Manager replication environment. Do not include the local site (the writer site) in the list.

groupname

Name of the master group to be created. This group will appear as a regular replication master group, and it can be managed from all the Oracle replication interfaces, including Oracle Enterprise Manager.

groupdescription

Description of the new master group. The default is Replication Group for OWM.

Usage Notes

To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. 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:

This procedure performs the following operations:

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

Purpose

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

Purpose

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

Format

DBMS_WM.GetDiffVersions() RETURN VARCHAR2;

Parameters

None.

Usage Notes

The returned string is in the format '(WS1,SP1), (WS2,SP2)'. This format, including the parentheses, is intended to help you if you later want to use parts of the returned string in a call to the SetDiffVersions procedure.

Examples

The following example displays the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions operation.

SELECT DBMS_WM.GetDiffVersions FROM DUAL;

GETDIFFVERSIONS                                                                 
--------------------------------------------------------------------------------
(B_focus_1, LATEST), (B_focus_2, LATEST) 

GetLockMode

Purpose

Returns the locking mode for the current session, which determines whether or not access is enabled to versioned rows and corresponding rows in the previous version.

Format

DBMS_WM.GetLockMode() RETURN VARCHAR2;

Parameters

None.

Usage Notes

This function returns E, S, C, or NULL.

For an explanation of Workspace Manager locking, see Section 1.3. See also the descriptions of the SetLockingON and SetLockingOFF procedures.

Examples

The following example displays the locking mode in effect for the session.

SELECT DBMS_WM.GetLockMode FROM DUAL;

GETLOCKMODE                                                                     
--------------------------------------------------------------------------------
C

GetMultiWorkspaces

Purpose

Returns the names of workspaces visible in the multiworkspace views for version-enabled tables.

Format

DBMS_WM.GetMultiWorkspaces() RETURN VARCHAR2;

Parameters

None.

Usage Notes

This procedure returns the names of workspaces visible in the multiworkspace views, which are described in Section 3.29.

If no workspaces are visible in the multiworkspace views, NULL is returned. If more than one workspace name is returned, names are separated by a comma (for example: workspace1,workspace2,workspace3).

To make a workspace visible in the multiworkspace views, use the SetMultiWorkspaces procedure.

Examples

The following example displays the names of workspaces visible in the multiworkspace views.

SELECT DBMS_WM.GetMultiWorkspaces FROM DUAL;


GetOpContext

Purpose

Returns the context of the current operation for the current session.

Format

DBMS_WM.GetOpContext() RETURN VARCHAR2;

Parameters

None.

Usage Notes

This function returns one of the following values:

The returned value can be used in user-defined triggers to take appropriate action based on the current operation.

Examples

The following example displays the context of the current operation.

SELECT DBMS_WM.GetOpContext FROM DUAL;

GETOPCONTEXT                                                                     
--------------------------------------------------------------------------------
DML

GetPrivs

Purpose

Returns a comma-delimited list of all privileges that the current user has for the specified workspace.

Format

DBMS_WM.GetPrivs(
   workspace  IN VARCHAR2) RETURN VARCHAR2;

Parameters

Table 2-17 GetPrivs Function Parameters  
Parameter Description
workspace

Name of the workspace for which to return the list of privileges. The name is case sensitive.

Usage

For information about Workspace Manager privileges, see Section 1.4.

Examples

The following example displays the privileges that the current user has for the B_focus_2 workspace.

SELECT DBMS_WM.GetPrivs ('B_focus_2') FROM DUAL;

DBMS_WM.GETPRIVS('B_FOCUS_2')                                                        
--------------------------------------------------------------------------------
ACCESS,MERGE,CREATE,REMOVE,ROLLBACK 

GetSessionInfo

Purpose

Retrieves information about the current workspace and session context.

Format

DBMS_WM.GetSessionInfo(
   workspace     OUT VARCHAR2,
   context       OUT VARCHAR2,
   context_type  OUT VARCHAR2);

Parameters

Table 2-18 GetSessionInfo Procedure Parameters  
Parameter Description
workspace

Name of the workspace that the current session is in.

context

The context of the current session in the workspace, expressed as one of the following: LATEST, a savepoint name, or an instant (point in time) in 'DD-MON-YYYY HH24:MI:SS' date format. (See the Usage Notes for details.)

context_
type

The type of context for the current session in the workspace. Specifically, one of the following values: LATEST (if context is LATEST), SAVEPOINT (if context is a savepoint name), or INSTANT (if context is an instant).

Usage Notes

This procedure is useful if you need to know where a session is (workspace and context) -- for example, after you have performed a combination of GotoWorkspace, GotoSavepoint, and GotoDate operations.

After the procedure successfully executes, the context parameter contains one of the following values:

For detailed information about the session context, see Section 1.2.

Examples

The following example retrieves and displays information about the current workspace and context in the session.

DECLARE
  current_workspace VARCHAR2(30);
  current_context VARCHAR2(30);
  current_context_type VARCHAR2(30);
BEGIN
  DBMS_WM.GetSessionInfo(current_workspace, 
                         current_context, 
                         current_context_type);
  DBMS_OUTPUT.PUT_LINE('Session currently in workspace: ' ||current_workspace);
  DBMS_OUTPUT.PUT_LINE('Session context is: ' ||current_context);
  DBMS_OUTPUT.PUT_LINE('Session context is on: ' ||current_context_type);
END;
/
Session currently in workspace: B_focus_2                                       
Session context is: LATEST                                                      
Session context is on: LATEST                                                   

PL/SQL procedure successfully completed.

GetWorkspace

Purpose

Returns the current workspace for the session.

Format

DBMS_WM.GetWorkspace() RETURN VARCHAR2;

Parameters

None.

Usage Notes

None.

Examples

The following example displays the current workspace for the session.

SELECT DBMS_WM.GetWorkspace FROM DUAL;

GETWORKSPACE                                                                        
--------------------------------------------------------------------------------
B_focus_2  

GotoDate

Purpose

Goes to a point at or near the specified date and time in the current workspace.

Syntax

DBMS_WM.GotoDate(
   in_date  IN DATE);

Parameters

Table 2-19 GotoDate Procedure Parameters  
Parameter Description
in_date

Date and time for the read-only view of the workspace. (See the Usage Notes for details.)

Usage Notes

You are presented a read-only view of the current workspace at or near the specified date and time. The exact time point depends on the history option for tracking changes to data in version-enabled tables, as set by the EnableVersioning procedure or modified by the SetWoOverwriteOFF or SetWoOverwriteON procedure:

For an explanation of the history options, see the description of the hist parameter for the EnableVersioning procedure.

The following example scenario shows the effect of the VIEW_WO_OVERWRITE setting. Assume the following sequence of events:

  1. The MANAGER_NAME value in a row is Adams.
  2. Savepoint SP1 is created.
  3. The MANAGER_NAME value is changed to Baxter.
  4. The time point that will be specified as in_date (in step 7) occurs.
  5. The MANAGER_NAME value is changed to Chang. (Thus, the value has been changed both before and after in_date since the first savepoint and before the second savepoint.)
  6. Savepoint SP2 is created.
  7. A GotoDate operation is executed, specifying the time point in step 4 as in_date.

In the preceding scenario, if the history option in effect is VIEW_WO_OVERWRITE, the MANAGER_NAME value after step 7 is Baxter.

The GotoDate procedure should be executed while users exist in the workspace. There are no explicit privileges associated with this procedure.

Examples

The following example goes to a point at or near midnight at the start of 29-Jun-2001, depending on the history option currently in effect.

EXECUTE DBMS_WM.GotoDate ('29-JUN-01');

GotoSavepoint

Purpose

Goes to the specified savepoint in the current workspace.

Syntax

DBMS_WM.GotoSavePoint(
   [savepoint_name  IN VARCHAR2 DEFAULT 'LATEST']);

Parameters

Table 2-20 GotoSavepoint Procedure Parameters  
Parameter Description
savepoint_name

Name of the savepoint. The name is case sensitive. If savepoint_name is not specified, the default is LATEST.

Usage Notes

You are presented a read-only view of the workspace at the time of savepoint creation. This procedure is useful for examining the workspace from different savepoints before performing a rollback to a specific savepoint by calling the RollbackToSP procedure to delete all rows from that savepoint forward.

This operation can be executed while users exist in the workspace. There are no explicit privileges associated with this operation.

If you do not want to roll back to the savepoint, you can call the GotoSavepoint procedure with a null parameter to go to the currently active version in the workspace. (This achieves the same result as calling the GotoWorkspace procedure and specifying the workspace.)

For more information about savepoints, including the LATEST savepoint, see Section 1.1.2.

Examples

The following example goes to the savepoint named Savepoint1.

EXECUTE DBMS_WM.GotoSavepoint ('Savepoint1');

GotoWorkspace

Purpose

Moves the current session to the specified workspace.

Syntax

DBMS_WM.GotoWorkspace(
   workspace  IN VARCHAR2);

Parameters

Table 2-21 GotoWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

After a user goes to a workspace, modifications to data can be made there.

To go to the live database, specify workspace as LIVE. Because many operations are prohibited when any users (including you) are in the workspace, it is often convenient to go to the LIVE workspace before performing operations on created workspaces.

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

Examples

The following example includes the user in the NEWWORKSPACE workspace. The user will begin to work in the latest version in that workspace.

EXECUTE DBMS_WM.GotoWorkspace ('NEWWORKSPACE');

The following example includes the user in the LIVE database workspace. By default, when users connect to a database, they are placed in this workspace.

EXECUTE DBMS_WM.GotoWorkspace ('LIVE');

GrantSystemPriv

Purpose

Grants system-level privileges (not restricted to a particular workspace) to users and roles. The grant_option parameter enables the grantee to grant the specified privileges to other users and roles.

Syntax

DBMS_WM.GrantSystemPriv(
   priv_types       IN VARCHAR2,
   grantee          IN VARCHAR2
   [, grant_option  IN VARCHAR2 DEFAULT 'NO']
   [, auto_commit   IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-22 GrantSystemPriv Procedure Parameters  
Parameter Description
priv_types

A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE, and FREEZE_ANY_WORKSPACE.

grantee

Name of the user (can be the PUBLIC user group) or role to which to grant priv_types.

grant_option

Specify YES to enable the grant option for grantee, or NO (the default) to disable the grant option for grantee. The grant option allows grantee to grant the privileges specified in priv_types to other users and roles.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

Contrast this procedure with GrantWorkspacePriv, which grants workspace-level Workspace Manager privileges with keywords that do not contain ANY and which has a workspace parameter.

If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT has been granted the ACCESS_ANY_WORKSPACE privilege with grant_option as NO, but that the PUBLIC user group has been granted the ACCESS_ANY_WORKSPACE privilege with grant_option as YES. Because user SCOTT is a member of PUBLIC, user SCOTT has the ACCESS_ANY_WORKSPACE privilege with the grant option.

The WM_ADMIN_ROLE role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE role is automatically given to the DBA role.

The ACCESS_WORKSPACE or ACCESS_ANY_WORKSPACE privilege is needed for all other Workspace Manager privileges.

To revoke system-level privileges, use the RevokeSystemPriv procedure.

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

Examples

The following example enables user Smith to access any workspace in the database, but does not allow Smith to grant the ACCESS_ANY_WORKSPACE privilege to other users.

EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE', 'Smith', 'NO');

GrantWorkspacePriv

Purpose

Grants workspace-level privileges to users and roles. The grant_option parameter enables the grantee to grant the specified privileges to other users and roles.

Syntax

DBMS_WM.GrantWorkspacePriv(
   priv_types       IN VARCHAR2,
   workspace        IN VARCHAR2,
   grantee          IN VARCHAR2
   [, grant_option  IN VARCHAR2 DEFAULT 'NO']
   [, auto_commit   IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-23 GrantWorkspacePriv Procedure Parameters  
Parameter Description
priv_types

A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are ACCESS_WORKSPACE, MERGE_WORKSPACE, CREATE_WORKSPACE, REMOVE_WORKSPACE, ROLLBACK_WORKSPACE, and FREEZE_WORKSPACE.

workspace

Name of the workspace. The name is case sensitive.

grantee

Name of the user (can be the PUBLIC user group) or role to which to grant priv_types.

grant_option

Specify YES to enable the grant option for grantee, or NO (the default) to disable the grant option for grantee. The grant option allows grantee to grant the privileges specified in priv_types on the workspace specified in workspace to other users and roles.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

Contrast this procedure with GrantSystemPriv, which grants system-level Workspace Manager privileges with keywords in the form xxx_ANY_WORKSPACE (ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, and so on).

If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT has been granted the ACCESS_WORKSPACE privilege with grant_option as NO, but that the PUBLIC user group has been granted the ACCESS_WORKSPACE privilege with grant_option as YES. Because user SCOTT is a member of PUBLIC, user SCOTT has the ACCESS_WORKSPACE privilege with the grant option.

The WM_ADMIN_ROLE role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE role is automatically given to the DBA role.

The ACCESS_WORKSPACE or ACCESS_ANY_WORKSPACE privilege is needed for all other Workspace Manager privileges.

To revoke workspace-level privileges, use the RevokeWorkspacePriv procedure.

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

Examples

The following example enables user Smith to access the NEWWORKSPACE workspace and merge changes in that workspace, and allows Smith to grant the two specified privileges on NEWWORKSPACE to other users.

DBMS_WM.GrantWorkspacePriv ('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 
'Smith', 'YES');

IsWorkspaceOccupied

Purpose

Checks whether or not a workspace has any active sessions.

Syntax

DBMS_WM.IsWorkspaceOccupied(
   workspace  IN VARCHAR2) RETURN VARCHAR2;

Parameters

Table 2-24 IsWorkspaceOccupied Function Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

This function returns YES if the workspace has any active sessions, and it returns NO if the workspace has no active sessions.

An exception is raised if the LIVE workspace is specified or if the user does not have the privilege to access the workspace.

Examples

The following example checks if any sessions are in the B_focus_2 workspace.

SELECT DBMS_WM.IsWorkspaceOccupied('B_focus_2') FROM DUAL;

DBMS_WM.ISWORKSPACEOCCUPIED('B_FOCUS_2')                                                 
--------------------------------------------------------------------------------
YES     

LockRows

Purpose

Controls access to versioned rows in a specified table and to corresponding rows in the parent workspace.

Syntax

DBMS_WM.LockRows(
   workspace        IN VARCHAR2,
   table_name       IN VARCHAR2
   [, where_clause  IN VARCHAR2 DEFAULT '']
   [, lock_mode     IN VARCHAR2 DEFAULT 'E']);

Parameters

Table 2-25 LockRows Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The latest versions of rows visible from the workspace are locked. If a row has not been modified in this workspace, the locked version could be in an ancestor workspace. The name is case sensitive.

table_name

Name of the table in which rows are to be locked. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be locked. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows in table_name are locked.

lock_mode

Mode with which to set the locks: E (exclusive) or S (shared). The default is E.

Usage Notes

This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle server locking. For an explanation of Workspace Manager locking, see Section 1.3.

This procedure does not affect whether Workspace Manager locking is set on or off (determined by the SetLockingON and SetLockingOFF procedures).

To unlock rows, use the UnlockRows procedure.

Examples

The following example locks rows in the EMPLOYEES table where last_name = 'Smith' in the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.LockRows ('NEWWORKSPACE', 'employees', 'last_name = ''Smith''');

MergeTable

Purpose

Applies changes to a table (all rows or as specified in the WHERE clause) in a workspace to its parent workspace.

Syntax

DBMS_WM.MergeTable(
   workspace            IN VARCHAR2,
   table_id             IN VARCHAR2
   [, where_clause      IN VARCHAR2 DEFAULT '']
   [, create_savepoint  IN BOOLEAN DEFAULT FALSE]
   [, remove_data       IN BOOLEAN DEFAULT FALSE]
   [, auto_commit       IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-26 MergeTable Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

table_id

Name of the table containing rows to be merged into the parent workspace. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be merged into the parent workspace. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows in table_name are merged.

create_savepoint

A Boolean value (TRUE or FALSE).

TRUE creates an implicit savepoint in the parent workspace before the merge operation. (Implicit and explicit savepoints are described in Section 1.1.2.)

FALSE (the default) does not create an implicit savepoint in the parent workspace before the merge operation.

remove_data

A Boolean value (TRUE or FALSE).

TRUE removes the data in the table (as specified by where_clause) in the child workspace. This option is permitted only if workspace has no child workspaces (that is, it is a leaf workspace).

FALSE (the default) does not remove the data in the table in the child workspace.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

All data that satisfies the where_clause in the version-enabled table table_name in workspace is applied to the parent workspace of workspace.

Any locks that are held by rows being merged are released.

If there are conflicts between the workspace being merged and its parent workspace, the merge operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)

A table cannot be merged in the LIVE workspace (because that workspace has no parent workspace).

A table cannot be merged or refreshed if there is an open database transaction affecting the table.

An exception is raised if the user does not have access to table_id, or if the user does not have the MERGE_WORKSPACE privilege for workspace or the MERGE_ANY_WORKSPACE privilege.

Examples

The following example merges changes to the EMP table (in the USER3 schema) where last_name = 'Smith' in NEWWORKSPACE to its parent workspace.

EXECUTE DBMS_WM.MergeTable ('NEWWORKSPACE', 'user3.emp', 'last_name = 
''Smith''');

MergeWorkspace

Purpose

Applies all changes in a workspace to its parent workspace, and optionally removes the workspace.

Syntax

DBMS_WM.MergeWorkspace(
   workspace            IN VARCHAR2
   [, create_savepoint  IN BOOLEAN DEFAULT FALSE]
   [, remove_workspace  IN BOOLEAN DEFAULT FALSE]
   [, auto_commit       IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-27 MergeWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

create_savepoint

A Boolean value (TRUE or FALSE).

TRUE creates an implicit savepoint in the parent workspace before the merge operation. (Implicit and explicit savepoints are described in Section 1.1.2.)

FALSE (the default) does not create an implicit savepoint in the parent workspace before the merge operation.

remove_workspace

A Boolean value (TRUE or FALSE).

TRUE removes workspace after the merge operation.

FALSE (the default) does not remove workspace after the merge operation; the workspace continues to exist.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

All data in all version-enabled tables in workspace is merged to the parent workspace of workspace, and workspace is removed if remove_workspace is TRUE.

While this procedure is executing, the current workspace is frozen in NO_ACCESS mode and the parent workspace is frozen in READ_ONLY mode, as explained in Section 1.1.5.

If there are conflicts between the workspace being merged and its parent workspace, the merge operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)

If the remove_workspace parameter value is TRUE, the workspace to be merged must be a leaf workspace, that is, a workspace with no descendant workspaces. (For an explanation of workspace hierarchy, see Section 1.1.1.)

An exception is raised if the user does not have the MERGE_WORKSPACE privilege for workspace or the MERGE_ANY_WORKSPACE privilege.

Examples

The following example merges changes in NEWWORKSPACE to its parent workspace and removes (by default) NEWWORKSPACE.

EXECUTE DBMS_WM.MergeWorkspace (`NEWWORKSPACE');

RecoverAllMigratingTables

Purpose

Attempts to complete the migration process on all tables that were left in an inconsistent state after the Workspace Manager migration procedure failed.

Syntax

DBMS_WM.RecoverAllMigratingTables(
   [, ignore_last_error IN BOOLEAN DEFAULT FALSE]);

Parameters

Table 2-28 RecoverAllMigratingTables Procedure Parameters  
Parameter Description
ignore_
last_error

A Boolean value (TRUE or FALSE).

TRUE ignores the last error, if any, that occurred during the migration process. Information about the last error is stored in the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views, which are described in Chapter 3. (See the Usage Notes for more information.)

FALSE (the default) does not ignore the last error, if any, that occurred during the migration process.

Usage Notes

If an error occurs while you are upgrading (migrating) version to the current Workspace Manager release, one or more version-enabled tables can be left in an inconsistent state. (For information about upgrading to the current release, see Section B.1.) If the upgrade procedure fails, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS or ALL_WM_VT_ERRORS metadata view to see the SQL statement and error message), and then call the RecoverMigratingTable procedure (for a single table) or RecoverAllMigratingTables procedure (for all tables) with the default ignore_last_error parameter value of FALSE, to try to complete the upgrade process.

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 RecoverMigratingTable or RecoverAllMigratingTables 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.

Examples

The following example attempts to recover all version-enabled tables that were left in an inconsistent state when the upgrade procedure failed.

EXECUTE DBMS_WM.RecoverAllMigratingTables;

The following example attempts to recover all version-enabled tables that were left in an inconsistent state when the upgrade procedure failed, and it ignores the last error that caused the upgrade procedure to fail.

EXECUTE DBMS_WM.RecoverAllMigratingTables(TRUE);

RecoverMigratingTable

Purpose

Attempts to complete the migration process on a table that was left in an inconsistent state after the Workspace Manager migration procedure failed.

Syntax

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

Parameters

Table 2-29 RecoverMigratingTable Procedure Parameters  
Parameter Description
table_name

Name of the version-enabled table to be recovered from the migration error. The name is not case sensitive.

ignore_
last_error

A Boolean value (TRUE or FALSE).

TRUE ignores the last error, if any, that occurred during the migration process. Information about the last error is stored in the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views, which are described in Chapter 3. (See the Usage Notes for more information.)

FALSE (the default) does not ignore the last error, if any, that occurred during the migration process.

Usage Notes

If an error occurs while you are upgrading to the current Workspace Manager release, one or more version-enabled tables can be left in an inconsistent state. (For information about upgrading to the current release, see Section B.1.) If the upgrade procedure fails, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS or ALL_WM_VT_ERRORS metadata view to see the SQL statement and error message), and then call the RecoverMigratingTable procedure (for a single table) or RecoverAllMigratingTables procedure (for all tables) with the default ignore_last_error parameter value of FALSE, to try to complete the upgrade process.

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 RecoverMigratingTable or RecoverAllMigratingTables 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 table_name does not exist or is not version-enabled.

Examples

The following example attempts to recover the COLA_MARKETING_BUDGET table from the error that caused the upgrade procedure to fail.

EXECUTE DBMS_WM.RecoverMigratingTable('COLA_MARKETING_BUDGET');

The following example attempts to recover the COLA_MARKETING_BUDGET table and ignores the last error that caused the upgrade procedure to fail.

EXECUTE DBMS_WM.RecoverMigratingTable('COLA_MARKETING_BUDGET', TRUE);

RefreshTable

Purpose

Applies to a workspace all changes made to a table (all rows or as specified in the WHERE clause) in its parent workspace.

Syntax

DBMS_WM.RefreshTable(
   workspace        IN VARCHAR2,
   table_id         IN VARCHAR2
   [, where_clause  IN VARCHAR2 DEFAULT '']
   [, auto_commit   IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-30 RefreshTable Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

table_id

Name of the table containing the rows to be refreshed using values from the parent workspace. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be refreshed from the parent workspace. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows in table_name are refreshed.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

This procedure applies to workspace all changes in rows that satisfy the where_clause in the version-enabled table table_id in the parent workspace since the time when workspace was created or last refreshed.

If there are conflicts between the workspace being refreshed and its parent workspace, the refresh operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)

A table cannot be refreshed in the LIVE workspace (because that workspace has no parent workspace).

A table cannot be merged or refreshed if there is an open database transaction affecting the table.

An exception is raised if the user does not have access to table_id, or if the user does not have the MERGE_WORKSPACE privilege for workspace or the MERGE_ANY_WORKSPACE privilege.

Examples

The following example refreshes NEWWORKSPACE by applying changes made to the EMPLOYEES table where last_name = 'Smith' in its parent workspace.

EXECUTE DBMS_WM.RefreshTable ('NEWWORKSPACE', 'employees', 'last_name = 
''Smith''');

RefreshWorkspace

Purpose

Applies to a workspace all changes made in its parent workspace.

Syntax

DBMS_WM.RefreshWorkspace(
   workspace      IN VARCHAR2
   [, auto_commit IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-31 RefreshWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

This procedure applies to workspace all changes made to version-enabled tables in the parent workspace since the time when workspace was created or last refreshed.

If there are conflicts between the workspace being refreshed and its parent workspace, the refresh operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)

The specified workspace and the parent workspace are frozen in READ_ONLY mode, as explained in Section 1.1.5.

The LIVE workspace cannot be refreshed (because it has no parent workspace).

An exception is raised if the user does not have the MERGE_WORKSPACE privilege for workspace or the MERGE_ANY_WORKSPACE privilege.

Examples

The following example refreshes NEWWORKSPACE by applying changes made in its parent workspace.

EXECUTE DBMS_WM.RefreshWorkspace (`NEWWORKSPACE');

RelocateWriterSite

Purpose

Makes one of the nonwriter sites the new writer site in a Workspace Manager replication environment. (The old writer site becomes one of the nonwriter sites.)

Syntax

DBMS_WM.RelocateWriterSite(
   newwritersite           IN VARCHAR2,
   oldwritersiteavailable  IN BOOLEAN);

Parameters

Table 2-32 RelocateWriterSite Procedure Parameters  
Parameter Description
newwritersite

Name of a current nonwriter site names (database link) to be made the new writer site in the Workspace Manager replication environment.

oldwritersiteavailable

A Boolean value (TRUE or FALSE).

TRUE causes the old writer site to be updated to reflect the fact that the writer site has changed.

FALSE causes the old writer site not to be updated to reflect the fact that the writer site has changed. In this case, you must use the SynchronizeSite procedure when the old writer site becomes available.

Usage Notes

To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. 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. You can execute it at any master site.

You should specify the oldwritersiteavailable parameter as TRUE if the old writer site is currently available. If you specify the oldwritersiteavailable parameter as FALSE, you must execute the SynchronizeSite procedure after the old writer site becomes available, to bring that site up to date.

This procedure performs the following operations:

Examples

The following example relocates the writer site for the Workspace Manager environment to BACKUP-SITE1 at a hypothetical company.

DBMS_WM.RelocateWriterSite(
    newwritersite           =>  `BACKUP-SITE1.ACME.COM');
    oldwritersiteavailable  =>  TRUE);

RemoveWorkspace

Purpose

Discards all row versions associated with a workspace and deletes the workspace.

Syntax

DBMS_WM.RemoveWorkspace(
   workspace       IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-33 RemoveWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

The RemoveWorkspace operation can only be performed on leaf workspaces (the bottom-most workspaces in a branch in the hierarchy). For an explanation of database workspace hierarchy, see Section 1.1.1.

There must be no other users in the workspace being removed.

An exception is raised if the user does not have the REMOVE_WORKSPACE privilege for workspace or the REMOVE_ANY_WORKSPACE privilege.

Examples

The following example removes the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.RemoveWorkspace('NEWWORKSPACE');

RemoveWorkspaceTree

Purpose

Discards all row versions associated with a workspace and its descendant workspaces, and deletes the affected workspaces.

Syntax

DBMS_WM.RemoveWorkspaceTree(
   workspace       IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-34 RemoveWorkspaceTree Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

The RemoveWorkspaceTree operation should be used with extreme caution, because it removes support structures and rolls back changes in a workspace and all its descendants down to the leaf workspace or workspaces. For example, in the hierarchy shown in Figure 1-1 in Section 1.1.1, a RemoveWorkspaceTree operation specifying Workspace1 removes Workspace1, Workspace2, and Workspace3. (For an explanation of database workspace hierarchy, see Section 1.1.1.)

There must be no other users in workspace or any of its descendant workspaces.

An exception is raised if the user does not have the REMOVE_WORKSPACE privilege for workspace or any of its descendant workspaces.

Examples

The following example removes the NEWWORKSPACE workspace and all its descendant workspaces.

EXECUTE DBMS_WM.RemoveWorkspaceTree('NEWWORKSPACE');

ResolveConflicts

Purpose

Resolves conflicts between workspaces.

Syntax

DBMS_WM.ResolveConflicts(
   workspace     IN VARCHAR2,
   table_name    IN VARCHAR2,
   where_clause  IN VARCHAR2,
   keep          IN VARCHAR2);

Parameters

Table 2-35 ResolveConflicts Procedure Parameters  
Parameter Description
workspace

Name of the workspace to check for conflicts with other workspaces. The name is case sensitive.

table_name

Name of the table to check for conflicts. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be refreshed from the parent workspace. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

keep

Workspace in favor of which to resolve conflicts: PARENT, CHILD, or BASE.

PARENT causes the parent workspace rows to be copied to the child workspace.

CHILD does not cause the child workspace rows to be copied immediately to the parent workspace. However, the conflict is considered resolved, and the child workspace rows are copied to the parent workspace when the child workspace is merged.

BASE causes the base rows to be copied to the child workspace but not to the parent workspace. However, the conflict is considered resolved; and when the child workspace is merged, the base rows are copied to the parent workspace. Note that BASE is ignored for insert-insert conflicts where a base row does not exist; in this case the keep parameter value must be PARENT or CHILD.

Usage Notes

This procedure checks the condition identified by table_name and where_clause, and it finds any conflicts between row values in workspace and its parent workspace. This procedure resolves conflicts by using the row values in the parent or child workspace, as specified in the keep parameter; however, the conflict resolution is not actually merged until you commit the transaction (standard database commit operation) and call the CommitResolve procedure to end the conflict resolution session. (For more information about conflict resolution, including an overall view of the process, see Section 1.1.4.)

For example, assume that for Department 20 (DEPARTMENT_ID = 20), the MANAGER_NAME in the LIVE and Workspace1 workspaces is Tom. Then, the following operations occur:

  1. The manager_name for Department 20 is changed in the LIVE database workspace from Tom to Mary.
  2. The change is committed (a standard database commit operation).
  3. The manager_name for Department 20 is changed in Workspace1 from Tom to Franco.
  4. The MergeWorkspace procedure is called to merge Workspace1 changes to the LIVE workspace.

    At this point, however, a conflict exists with respect to MANAGER_NAME for Department 20 in Workspace1 (Franco, which conflicts with Mary in the LIVE workspace), and therefore the call to MergeWorkspace does not succeed.

  5. The ResolveConflicts procedure is called with the following parameters: ('Workspace1', 'department', 'department_id = 20', 'child').

    After the MergeWorkspace operation in step 7, the MANAGER_NAME value will be Franco in both the Workspace1 and LIVE workspaces.

  6. The change is committed (a standard database commit operation).
  7. The MergeWorkspace procedure is called to merge Workspace1 changes to the LIVE workspace.

For more information about conflict resolution, see Section 1.1.4.

Examples

The following example resolves conflicts involving rows in the DEPARTMENT table in Workspace1 where DEPARTMENT_ID is 20, and uses the values in the child workspace to resolve all such conflicts. It then merges the results of the conflict resolution by first committing the transaction (standard commit) and then calling the MergeWorkspace procedure.

EXECUTE DBMS_WM.BeginResolve ('Workspace1');
EXECUTE  DBMS_WM.ResolveConflicts ('Workspace1', 'department', 'department_id = 
20', 'child');
COMMIT;
EXECUTE DBMS_WM.CommitResolve ('Workspace1');

RevokeSystemPriv

Purpose

Revokes (removes) system-level privileges from users and roles.

Syntax

DBMS_WM.RevokeSystemPriv(
   priv_types      IN VARCHAR2,
   grantee         IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-36 RevokeSystemPriv Procedure Parameters  
Parameter Description
priv_types

A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, and ROLLBACK_ANY_WORKSPACE.

grantee

Name of the user (can be the PUBLIC user group) or role from which to revoke priv_types.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

Contrast this procedure with RevokeWorkspacePriv, which revokes workspace-level Workspace Manager privileges with keywords in the form xxx_WORKSPACE (ACCESS_WORKSPACE, MERGE_WORKSPACE, and so on).

To grant system-level privileges, use the GrantSystemPriv procedure.

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

Examples

The following example disallows user Smith from accessing workspaces and merging changes in workspaces.

EXECUTE DBMS_WM.RevokeSystemPriv (`ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE', 
`Smith');

RevokeWorkspacePriv

Purpose

Revokes (removes) workspace-level privileges from users and roles for a specified workspace.

Syntax

DBMS_WM.RevokeWorkspacePriv(
   priv_types      IN VARCHAR2,
   workspace       IN VARCHAR2,
   grantee         IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-37 RevokeWorkspacePriv Procedure Parameters  
Parameter Description
priv_types

A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are ACCESS_WORKSPACE, MERGE_WORKSPACE, CREATE_WORKSPACE, REMOVE_WORKSPACE, and ROLLBACK_WORKSPACE.

workspace

Name of the workspace. The name is case sensitive.

grantee

Name of the user (can be the PUBLIC user group) or role from which to revoke priv_types.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

Contrast this procedure with RevokeSystemPriv, which revokes system-level Workspace Manager privileges with keywords in the form xxx_ANY_WORKSPACE (ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, and so on).

To grant workspace-level privileges, use the GrantWorkspacePriv procedure.

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

Examples

The following example disallows user Smith from accessing the NEWWORKSPACE workspace and merging changes in that workspace.

EXECUTE DBMS_WM.RevokeWorkspacePriv (`ACCESS_WORKSPACE, MERGE_WORKSPACE', 
`NEWWORKSPACE', `Smith');

RollbackDDL

Purpose

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

Syntax

DBMS_WM.RollbackDDL(
   table_name  IN VARCHAR2);

Parameters

Table 2-38 RollbackDDL Procedure Parameters  
Parameter Description
table_name

Name of the version-enabled table. The name is not case sensitive.

Usage Notes

This procedure rolls back (cancels) changes that were made to a version-enabled table and to any indexes and triggers 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, see Section 1.6; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.

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

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 canceling the change.

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

RollbackResolve

Purpose

Quits a conflict resolution session and discards all changes in the workspace since the BeginResolve procedure was executed.

Syntax

DBMS_WM.RollbackResolve(
   workspace  IN VARCHAR2);

Parameters

Table 2-39 RollbackResolve Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

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

While the conflict resolution session is being rolled back, the workspace is frozen in 1WRITER mode, as explained in Section 1.1.5.

For more information about conflict resolution, see Section 1.1.4.

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

Examples

The following example quits the conflict resolution session in Workspace1 and discards all changes.

EXECUTE  DBMS_WM.RollbackResolve ('Workspace1');

RollbackTable

Purpose

Discards all changes made in the workspace to a specified table (all rows or as specified in the WHERE clause).

Syntax

DBMS_WM.RollbackTable(
   workspace        IN VARCHAR2,
   table_id         IN VARCHAR2,
   [, sp_name       IN VARCHAR2 DEFAULT '']
   [, where_clause  IN VARCHAR2 DEFAULT '']
   [, remove_locks  IN BOOLEAN DEFAULT TRUE]
   [, auto_commit   IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-40 RollbackTable Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

table_id

Name of the table containing rows to be discarded. The name is not case sensitive.

sp_name

Name of the savepoint to which to roll back. The name is case sensitive. The default is to discard all changes (that is, ignore any savepoints).

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be discarded. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows that meet the criteria of the other parameters are discarded.

remove_locks

A Boolean value (TRUE or FALSE).

TRUE (the default) releases those locks on rows in the parent workspace that satisfy the condition in where_clause and that were not versioned in the child workspace. This option has no effect if a savepoint is specified (sp_name parameter).

FALSE does not release any locks in the parent workspace.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

You cannot roll back to a savepoint if any implicit savepoints have been created since the specified savepoint, unless you first merge or remove the descendant 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.

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

Examples

The following example rolls back all changes made to the EMP table (in the USER3 schema) in the NEWWORKSPACE workspace since that workspace was created.

EXECUTE DBMS_WM.RollbackTable ('NEWWORKSPACE', 'user3.emp');

RollbackToSP

Purpose

Discards all data changes made in the workspace to version-enabled tables since the specified savepoint.

Syntax

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

Parameters

Table 2-41 RollbackToSP Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

savepoint_name

Name of the savepoint to which to roll back changes. The name is case sensitive.

auto_commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

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

Contrast this procedure with RollbackWorkspace, which rolls back all changes made since the creation of the workspace.

You cannot roll back to a savepoint if any implicit savepoints have been created since the specified savepoint, unless you first merge or remove the descendant 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.

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

Examples

The following example rolls back any changes made in the NEWWORKSPACE workspace to all tables since the creation of Savepoint1.

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

RollbackWorkspace

Purpose

Discards all data changes made in the workspace to version-enabled tables.

Syntax

DBMS_WM.RollbackWorkspace(
   workspace       IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 2-42 RollbackWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

auto_
commit

A Boolean value (TRUE or FALSE).

TRUE (the default) causes the operation to be executed as an autonomous database transaction that will be committed when it finishes.

FALSE causes the operation to be executed as part of the caller's open database transaction (if one exists). If there is no open database transaction, the operation is executed in a new database transaction. In either case, the caller is responsible for committing the transaction. For more information, see Section 1.1.7.

Usage Notes

Only leaf workspaces can be rolled back. That is, a workspace cannot be rolled back if it has any descendant workspaces. (For an explanation of workspace hierarchy, see Section 1.1.1.)

Contrast this procedure with RollbackToSP, which rolls back changes to a specified savepoint.

Like the RemoveWorkspace procedure, RollbackWorkspace deletes the data in the workspace; however, unlike the RemoveWorkspace procedure, RollbackWorkspace does not delete the Workspace Manager workspace structure.

While this procedure is executing, the specified workspace is frozen in NO_ACCESS mode, as explained in Section 1.1.5.

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

Examples

The following example rolls back any changes made in the NEWWORKSPACE workspace since that workspace was created.

EXECUTE DBMS_WM.RollbackWorkspace ('NEWWORKSPACE');

SetConflictWorkspace

Purpose

Determines whether or not conflicts exist between a workspace and its parent.

Syntax

DBMS_WM.SetConflictWorkspace(
   workspace  IN VARCHAR2);

Parameters

Table 2-43 SetConflictWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

This procedure checks for any conflicts between workspace and its parent workspace, and it modifies the content of the <table_name>_CONF views (explained in Section 3.25) as needed.

A SELECT operation from the <table_name>_CONF views for all tables modified in a workspace displays all rows in the workspace that are in conflict with the parent workspace. (To obtain a list of tables that have conflicts for the current conflict workspace setting, use the SQL statement SELECT * FROM ALL_WM_VERSIONED_TABLES WHERE conflict = 'YES';. The SQL statement SELECT * FROM <table_name>_CONF displays conflicts for <table_name> between the current workspace and its parent workspace.)

Any conflicts must be resolved before a workspace can be merged or refreshed. To resolve a conflict, you must use the ResolveConflicts procedure (and then merge the result of the resolution by using the MergeWorkspace procedure).

Examples

The following example checks for any conflicts between B_focus_2 and its parent workspace, and modifies the contents of the <table_name>_CONF views as needed.

EXECUTE DBMS_WM.SetConflictWorkspace ('B_focus_2');

SetDiffVersions

Purpose

Finds differences in values in version-enabled tables for two savepoints and their common ancestor (base). It modifies the contents of the differences views that describe these differences.

Syntax

DBMS_WM.SetDiffVersions(
   workspace1  IN VARCHAR2,
   workspace2  IN VARCHAR2);

or

DBMS_WM.SetDiffVersions(
   workspace1  IN VARCHAR2,
   savepoint1  IN VARCHAR2,
   workspace2  IN VARCHAR2,
   savepoint2  IN VARCHAR2);

Parameters

Table 2-44 SetDiffVersions Procedure Parameters  
Parameter Description
workspace1

Name of the first workspace to be checked for differences in version-enabled tables. The name is case sensitive.

savepoint1

Name of the savepoint in workspace1 for which values are to be checked. The name is case sensitive.

If savepoint1 and savepoint2 are not specified, the rows in version-enabled tables for the LATEST savepoint in each workspace are checked. (The LATEST savepoint is explained in Section 1.1.2.)

workspace2

Name of the second workspace to be checked for differences in version-enabled tables. The name is case sensitive.

savepoint2

Name of the savepoint in workspace2 for which values are to be checked. The name is case sensitive.

Usage Notes

This procedure modifies the contents of the differences views (xxx_DIFF), which are described in Section 3.26. Each call to the procedure populates one or more sets of three rows, each set consisting of:

You can then select rows from the appropriate xxx_DIFF view or views to check comparable table values in the two savepoints and their common ancestor. The common ancestor (or base) is identified as DiffBase in xxx_DIFF view rows.

Examples

The following example checks the differences in version-enabled tables for the B_focus_1 and B_focus_2 workspaces. (The output has been reformatted for readability.)

SQL> -- Add rows to difference view: COLA_MARKETING_BUDGET_DIFF
SQL> EXECUTE DBMS_WM.SetDiffVersions ('B_focus_1', 'B_focus_2');

SQL> -- View the rows that were just added.
SQL> SELECT * from COLA_MARKETING_BUDGET_DIFF;
    
PRODUCT_ID  PRODUCT_NAME  MANAGER  BUDGET  WM_DIFFVER          WMCODE
----------  ------------  -------  ------  -----------         --------
       1        cola_a    Alvarez   2      DiffBase            NC
       1        cola_a    Alvarez   1.5    B_focus_1, LATEST   U       
       1        cola_a    Alvarez   2      B_focus_2, LATEST   NC      
       2        cola_b    Burton    2      DiffBase            NC      
       2        cola_b    Beasley   3      B_focus_1, LATEST   U       
       2        cola_b    Burton    2.5    B_focus_2, LATEST   U    
       3        cola_c    Chen      1.5    DiffBase            NC   
       3        cola_c    Chen      1      B_focus_1, LATEST   U   
       3        cola_c    Chen      1.5    B_focus_2, LATEST   NC  
       4        cola_d    Davis     3.5    DiffBase            NC  
       4        cola_d    Davis     3      B_focus_1, LATEST   U   
       4        cola_d    Davis     2.5    B_focus_2, LATEST   U   
         
12 rows selected.

Section 3.26 explains how to interpret and use the information in the differences (xxx_DIFF) views.


SetLockingOFF

Purpose

Disables Workspace Manager locking for the current session.

Syntax

DBMS_WM.SetLockingOFF();

Parameters

None.

Usage Notes

This procedure turns off Workspace Manager locking that had been set on by the SetLockingON procedure. Existing locks applied by this session remain locked. All new changes by this session are not locked.

Examples

The following example sets locking off for the session.

EXECUTE DBMS_WM.SetLockingOFF;

SetLockingON

Purpose

Enables Workspace Manager locking for the current session.

Syntax

DBMS_WM.SetLockingON(
   lockmode  IN VARCHAR2);

Parameters

Table 2-45 SetLockingON Procedure Parameters  
Parameter Description
lockmode

Locking mode. Must be E, S, or C.

E (exclusive) mode locks the rows in the previous version and the corresponding rows in the current version; no other users in the workspace for either version can change any values.

S (shared) mode locks the rows in the previous version and the corresponding rows in the current version; however, other users in the workspace for the current version (but no users in the workspace for the previous version) can change values in these rows.

C (carry-forward) mode locks rows in the current workspace with the same locking mode as the corresponding rows in the previous version. (If a row is not locked in the previous version, its corresponding row in the current version is not locked.)

Usage Notes

This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle server locking. Workspace Manager locks can be used to prevent conflicts. When a user locks a row, the corresponding row in the parent workspace is also locked. Thus, when this workspace merges with the parent at merge time, it is guaranteed that this row will not have a conflict.

Exclusive locking prevents the use of what-if scenarios in which different values for one or more columns are tested. Thus, plan any testing of scenarios when exclusive locking is not in effect.

Locking is enabled at the user session level, and the locking mode stays in effect until any of the following occurs:

The locks remain in effect for the duration of the workspace, unless unlocked by the UnlockRows procedure. (Existing locks are not affected by the SetLockingOFF procedure.)

There are no specific privileges associated with locking. Any session that can go to a workspace can set locking on.

Examples

The following example sets exclusive locking on for the session.

EXECUTE DBMS_WM.SetLockingON ('E');

All rows locked by this user remain locked until the workspace is merged or rolled back.


SetMultiWorkspaces

Purpose

Makes the specified workspace or workspaces visible in the multiworkspace views for version-enabled tables.

Syntax

DBMS_WM.SetMultiWorkspaces(
   workspaces  IN VARCHAR2);

Parameters

Table 2-46 SetMultiWorkspaces Procedure Parameters  
Parameter Description
workspaces

The workspace or workspaces for which information is to be added to the multiworkspace views (described in Section 3.29). The workspace names are case sensitive.

To specify more than one workspace (but no more than eight), use a comma to separate workspace names. For example: 'workspace1,workspace2'

Usage Notes

This procedure adds rows to the multiworkspace views (xxx_MW). See Section 3.29 for information about the contents and uses of these views.

To see the names of workspaces visible in the multiworkspace views, use the GetMultiWorkspaces function.

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

Examples

The following example adds information to the multiworkspace views for version-enabled tables in the B_focus_1 workspace.

SQL> EXECUTE DBMS_WM.SetMultiWorkspaces ('B_focus_1');

SetWoOverwriteOFF

Purpose

Disables the VIEW_WO_OVERWRITE history option that had been enabled by the EnableVersioning or SetWoOverwriteON procedure, changing the option to VIEW_W_OVERWRITE (with overwrite).

Syntax

DBMS_WM.SetWoOverwriteOFF();

Parameters

None.

Usage Notes

This procedure affects the recording of history information in the views named <table_name>_HIST by changing the VIEW_WO_OVERWRITE option to VIEW_W_OVERWRITE. That is, from this point forward, the views show only the most recent modifications to the same version of the table. A history of modifications to the version is not maintained; that is, subsequent changes to a row in the same version overwrite earlier changes.

This procedure affects only tables that were version-enabled with the hist parameter set to VIEW_WO_OVERWRITE in the call to the EnableVersioning procedure.

The <table_name>_HIST views are described in Section 3.28. The VIEW_WO_OVERWRITE and VIEW_W_OVERWRITE options are further described in the description of the EnableVersioning procedure.

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

The result of the SetWoOverwriteOFF procedure remains in effect only for the duration of the current session. To reverse the effect of this procedure, use the SetWoOverwriteON procedure.

Examples

The following example disables the VIEW_WO_OVERWRITE history option.

EXECUTE DBMS_WM.SetWoOverwriteOFF;

SetWoOverwriteON

Purpose

Enables the VIEW_WO_OVERWRITE history option that had been disabled by the SetWoOverwriteOFF procedure.

Syntax

DBMS_WM.SetWoOverwriteON();

Parameters

None.

Usage Notes

This procedure affects the recording of history information in the views named <table_name>_HIST by changing the VIEW_W_OVERWRITE option to VIEW_WO_OVERWRITE (without overwrite). That is, from this point forward, the views show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

This procedure affects only tables that were affected by a previous call to the SetWoOverwriteOFF procedure.

The <table_name>_HIST views are described in Section 3.28. The VIEW_WO_OVERWRITE and VIEW_W_OVERWRITE options are further described in the description of the EnableVersioning procedure.

The VIEW_WO_OVERWRITE history option can be overridden when a workspace is compressed by specifying the compress_view_wo_overwrite parameter as TRUE with the CompressWorkspace or CompressWorkspaceTree procedure.

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

To reverse the effect of this procedure, use the SetWoOverwriteOFF procedure.

Examples

The following example enables the VIEW_WO_OVERWRITE history option.

EXECUTE DBMS_WM.SetWoOverwriteON;

SetWorkspaceLockModeOFF

Purpose

Disables Workspace Manager locking for the specified workspace.

Syntax

DBMS_WM.SetWorkspaceLockModeOFF(
   workspace  IN VARCHAR2);

Parameters

Table 2-47 SetWorkspaceLockModeOFF Procedure Parameters  
Parameter Description
workspace

Name of the workspace for which to set the locking mode off. The name is case sensitive.

Usage Notes

This procedure turns off Workspace Manager locking that had been set on by the SetWorkspaceLockModeON procedure. Existing locks applied by this session remain locked. All new changes by this session or a subsequent session are not locked, unless the session turns locking on by executing the SetLockingON procedure.

An exception is raised if any of the following occurs:

Examples

The following example sets locking off for the workspace named NEWWORKSPACE.

EXECUTE DBMS_WM.SetWorkspaceLockModeOFF('NEWWORKSPACE');

SetWorkspaceLockModeON

Purpose

Enables Workspace Manager locking for the specified workspace.

Syntax

DBMS_WM.SetWorkspaceLockModeON(
   workspace    IN VARCHAR2,
   lockmode     IN VARCHAR2
   [, override  IN BOOLEAN DEFAULT FALSE]);

Parameters

Table 2-48 SetWorkspaceLockModeON Procedure Parameters  
Parameter Description
workspace

Name of the workspace for which to enable Workspace Manager locking. The name is case sensitive.

lockmode

Default locking mode for row-level locking. Must be E, S, or C.

E (exclusive) mode locks the rows in the parent workspace and the corresponding rows in the current workspace; no other users in either workspace can change any values.

S (shared) mode locks the rows in the parent workspace and the corresponding rows in the current workspace; however, other users in the current workspace (but no users in the parent workspace) can change values in these rows.

C (carry-forward) mode locks rows in the current workspace with the same locking mode as the corresponding rows in the parent workspace. (If a row is not locked in the parent workspace, its corresponding row in the child workspace is not locked.)

override

A Boolean value (TRUE or FALSE)

TRUE allows a session in the workspace to change the lockmode value by using the SetLockingON and SetLockingOFF procedures.

FALSE (the default) prevents a session in the workspace from changing the lockmode value.

Usage Notes

This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle server locking. Workspace Manager locks can be used to prevent conflicts. When a user locks a row, the corresponding row in the parent workspace is also locked. Thus, when this workspace merges with the parent at merge time, it is guaranteed that this row will not have a conflict.

Exclusive locking prevents the use of what-if scenarios in which different values for one or more columns are tested. Thus, plan any testing of scenarios when exclusive locking is not in effect.

If the override parameter value is TRUE, locking can also be enabled and disabled at the user session level with the SetLockingON and SetLockingOFF procedures, respectively.

All new changes by this session or a subsequent session are locked, unless the session turns locking off by executing the SetLockingOFF procedure.

An exception is raised if any of the following occurs:

Examples

The following example sets exclusive locking on for the workspace named NEWWORKSPACE.

EXECUTE DBMS_WM.SetWorkspaceLockModeON ('NEWWORKSPACE', 'E');

All locked rows remain locked until the workspace is merged or rolled back.


SynchronizeSite

Purpose

Brings the local site (the old writer site) up to date in the Workspace Manager replication environment after the writer site was moved using the RelocateWriterSite procedure.

Syntax

DBMS_WM.SynchronizeSite(
   newwritersite  IN VARCHAR2);

Parameters

Table 2-49 SynchronizeSite Procedure Parameters  
Parameter Description
newwritersite

Name of the new writer site (database link) with which the local site needs to be brought up to date.

Usage Notes

To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. 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.

You must execute this procedure on the old writer site if you specified the oldwritersiteavailable parameter as FALSE when you executed the RelocateWriterSite procedure.

Examples

The following example brings the local system up to date with the new writer site (BACKUP-SITE1.ACME.COM) in the Workspace Manager replication environment.

DBMS_WM.SynchronizeSite('BACKUP-SITE1.ACME.COM');

UnfreezeWorkspace

Purpose

Enables access and changes to a workspace, reversing the effect of the FreezeWorkspace procedure.

Syntax

DBMS_WM.UnfreezeWorkspace(
   workspace  IN VARCHAR2);

Parameters

Table 2-50 UnfreezeWorkspace Procedure Parameters  
Parameter Description
workspace

Name of the workspace. The name is case sensitive.

Usage Notes

The operation fails if any sessions are in workspace.

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

Examples

The following example unfreezes the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.UnfreezeWorkspace ('NEWWORKSPACE');

UnlockRows

Purpose

Enables access to versioned rows in a specified table and to corresponding rows in the parent workspace.

Syntax

DBMS_WM.UnlockRows(
   workspace        IN VARCHAR2,
   table_name       IN VARCHAR2
   [, where_clause  IN VARCHAR2 DEFAULT '']
   [, all_or_user   IN VARCHAR2 DEFAULT 'USER']
   [, lock_mode     IN VARCHAR2 DEFAULT 'ES']);

Parameters

Table 2-51 UnlockRows Procedure Parameters  
Parameter Description
workspace

Name of the workspace: locked rows in this workspace and corresponding rows in the parent workspace will be unlocked, as specified in the remaining parameters. The name is case sensitive.

table_name

Name of the table in which rows are to be unlocked. The name is not case sensitive.

where_clause

The WHERE clause (excluding the WHERE keyword) identifying the rows to be unlocked. Example: 'department_id = 20'

Only primary key columns can be specified in the WHERE clause. The WHERE clause cannot contain a subquery.

If where_clause is not specified, all rows in table_name are made accessible.

all_or_user

Scope of the request: ALL or USER.

ALL: All locks accessible by the user in the specified workspace are considered.

USER (default): Only locks owned by the user in the specified workspace are considered.

lock_mode

Locking mode: E, S, or ES.

E: Only exclusive mode locks are considered.

S: Only shared mode locks are considered.

ES (default): Both exclusive mode and shared mode locks are considered.

Usage Notes

This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle server locking. For an explanation of Workspace Manager locking, see Section 1.3.

This procedure unlocks rows that had been previously locked (see the LockRows procedure). It does not affect whether Workspace Manager locking is set on or off (determined by the SetLockingON and SetLockingOFF procedures).

Examples

The following example unlocks the EMPLOYEES table where last_name = 'Smith' in the NEWWORKSPACE workspace.

EXECUTE DBMS_WM.UnlockRows ('employees', 'NEWWORKSPACE', 'last_name = 
''Smith''');

Go to previous page Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback