Release 1 (9.0.1)
Part Number A88806-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Workspace Manager Release 1 (9.0.1) Part Number A88806-01 |
|
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.8.
Errors (exceptions) that can occur with Workspace Manager procedures are documented in Appendix B, including the cause and suggested user action for each error.
Syntax notes:
DBMS_WM public synonym for the Workspace Manager PL/SQL package must be used with the procedure name. The DBMS_WM public synonym is included in the Syntax and in any examples.
EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE'); EXECUTE dbms_wm.createworkspace ('NEWWORKSPACE'); EXECUTE dBms_Wm.cReatEwoRksPace ('NEWWORKSPACE');
Modifies the description of a savepoint.
DBMS_WM.AlterSavepoint( workspace IN VARCHAR2, sp_name IN VARCHAR2, sp_description IN VARCHAR2);
An exception is raised if the user is not the workspace owner or savepoint owner or does not have the WM_ADMIN_ROLE role.
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');
Modifies the description of a workspace.
DBMS_WM.AlterWorkspace( workspace IN VARCHAR2, workspace_description IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
workspace_description
|
Description of the workspace. |
An exception is raised if the user is not the workspace owner or does not have the WM_ADMIN_ROLE role.
The following example modifies the description of the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.AlterWorkspace (`NEWWORKSPACE', 'Testing proposed scenario B');
Starts a conflict resolution session.
DBMS_WM.BeginResolve( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
This procedure starts a conflict resolution session. While this procedure is executing, the workspace is frozen in 1WRITER mode, as explained in Section 1.2.6.
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.2.5.
An exception is raised if one or more of the following apply:
workspace.
workspace and its parent workspace.
The following example starts a conflict resolution session in Workspace1.
EXECUTE DBMS_WM.BeginResolve ('Workspace1');
Ends a conflict resolution session and saves (makes permanent) any changes in the workspace since the BeginResolve procedure was executed.
DBMS_WM.CommitResolve( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
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 RollbackResolve, which discards all changes.
For more information about conflict resolution, see Section 1.2.5.
An exception is raised if one or more of the following apply:
workspace.
WM_ADMIN_ROLE role or that did not execute the BeginResolve procedure on workspace.
The following example ends the conflict resolution session in Workspace1 and saves all changes.
EXECUTE DBMS_WM.CommitResolve ('Workspace1');
Deletes explicit savepoints in a workspace and minimizes the Workspace Manager metadata structures for the workspace.
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]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
compress_
view_wo_
overwrite
|
A Boolean value (
|
firstSP
|
First explicit savepoint. Savepoint names are case sensitive.
If only
If
If only |
secondSP
|
Second explicit savepoint. All explicit savepoints from Savepoint names are case sensitive. |
auto_
commit
|
A Boolean value (
|
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 READ_ONLY mode, as explained in Section 1.2.6.
A workspace cannot be compressed if there are any sessions with an open regular transaction, or if any user has executed a GotoDate operation or a GotoSavepoint operation specifying a savepoint in the workspace.
For information about VIEW_WO_OVERWRITE and other history options, see the information about the EnableVersioning procedure.
An exception is raised if the user does not have the privilege to access and merge changes in workspace.
To compress a workspace and all its descendant workspaces, use the CompressWorkspaceTree procedure.
The following example compresses NEWWORKSPACE.
EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE');
The following example compresses NEWWORKSPACE, deleting all explicit savepoints between the creation of the workspace and the savepoint SP1.
EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE', 'SP1');
The following example compresses NEWWORKSPACE, deleting the explicit savepoint SP1 and all explicit savepoints up to but not including SP2.
EXECUTE DBMS_WM.CompressWorkspace (`NEWWORKSPACE', 'SP1', 'SP2');
The following example compresses B_focus_1, accepts the default values for the firstSP and secondSP parameters (that is, deletes all explicit savepoints), and specifies FALSE for the auto_commit parameter.
EXECUTE DBMS_WM.CompressWorkspace ('B_focus_1', NULL, NULL, FALSE);
Deletes explicit savepoints in a workspace and all its descendant workspaces. 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.
DBMS_WM.CompressWorkspaceTree( workspace IN VARCHAR2 [, compress_view_wo_overwrite IN BOOLEAN DEFAULT FALSE] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
compress_
view_wo_
overwrite
|
A Boolean value (
|
auto_
commit
|
A Boolean value (
|
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.2.1, a CompressWorkspaceTree operation specifying Workspace 1 compresses Workspace 1, Workspace 2, and Workspace 3. (For an explanation of database workspace hierarchy, see Section 1.2.1.)
The compression operation is useful for the following reasons:
A workspace cannot be compressed if there are any sessions with an open regular 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 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.
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', NULL, FALSE);
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.
DBMS_WM.CopyForUpdate( table_name IN VARCHAR2, [, where_clause IN VARCHAR2 DEFAULT '']);
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.)
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 */ vm.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;
Creates a savepoint for the current version.
DBMS_WM.CreateSavepoint( workspace IN VARCHAR2, savepoint_name IN VARCHAR2 [, description IN VARCHAR2 DEFAULT NULL] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 (
|
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; a quiet point is not required.
While this procedure is executing, the current workspace is frozen in READ_ONLY mode, as explained in Section 1.2.6.
An exception is raised if one or more of the following apply:
workspace does not exist.
savepoint_name already exists.
The following example creates a savepoint named Savepoint1 in the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.CreateSavepoint ('NEWWORKSPACE', 'Savepoint1');
Creates a new workspace in the database.
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]);
| 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 (
If you use the syntax without the |
description
|
Description of the workspace. |
auto_commit
|
A Boolean value (
|
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.2.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.2.2.
While this procedure is executing, the current workspace is frozen in READ_ONLY mode, as explained in Section 1.2.6.
This procedure does not implicitly go to the workspace created. To go to the workspace, use the GotoWorkspace procedure.
An exception is raised if one or more of the following apply:
The following example creates a workspace named NEWWORKSPACE in the database.
EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE');
Deletes a savepoint.
DBMS_WM.DeleteSavepoint( workspace IN VARCHAR2, savepoint_name IN VARCHAR2) [, compress_view_wo_overwrite IN BOOLEAN DEFAULT FALSE] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 (
|
auto_commit
|
A Boolean value (
|
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:
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 regular 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:
workspace does not exist.
savepoint_name does not exist.
The following example deletes a savepoint named Savepoint1 in the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.DeleteSavepoint ('NEWWORKSPACE', 'Savepoint1');
Deletes all support structures that were created to enable the table to support versioned rows.
DBMS_WM.DisableVersioning( table_name IN VARCHAR2 [, force IN BOOLEAN DEFAULT FALSE]);
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.)
The DisableVersioning operation fails if the force value is FALSE and any of the following apply:
LIVE workspace.
LIVE workspace.
Only the owner of a table or a user with the WM_ADMIN_ROLE role can disable versioning on the table.
Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.
An exception is raised if the table is not version-enabled.
The following example disables the EMPLOYEE table for versioning.
EXECUTE DBMS_WM.DisableVersioning ('employee');
Creates the necessary structures to enable the table to support multiple versions of rows.
DBMS_WM.EnableVersioning( table_name IN VARCHAR2 [, hist IN VARCHAR2 DEFAULT 'NONE']);
| Parameter | Description |
|---|---|
table_name
|
Name of the table. The length of a table name must not exceed 25 characters. The name is not case sensitive. |
hist
|
History option, for tracking modifications to
|
The table that is being version-enabled must have a primary key defined.
Only the owner of a table 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 the table is already version-enabled.
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. However, the VIEW_WO_OVERWRITE hist 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 enables you to log and audit modifications.
The history option affects the behavior of the GotoDate procedure. See the Usage Notes for that procedure.
Current notes and restrictions include the following:
The following example enables versioning on the EMPLOYEE table.
EXECUTE DBMS_WM.EnableVersioning('employee');
Disables changes in a workspace and prevents subsequent sessions from entering the workspace.
DBMS_WM.FreezeWorkspace( workspace IN VARCHAR2 [, freezemode IN VARCHAR2 DEFAULT 'NO_ACCESS'] [, freezewriter IN VARCHAR2 DEFAULT NULL] [, force IN BOOLEAN DEFAULT FALSE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
freezemode
|
Mode for the frozen workspace. Must be one of the following values:
|
freezewriter
|
The user that is allowed to make changes in the workspace. Can be specified only if |
force
|
A Boolean value (
|
The operation fails if any sessions are active in workspace (unless force is TRUE) and freezemode is NO_ACCESS.
If freezemode is READ_ONLY or 1WRITER, the workspace cannot be frozen if there is an active regular transaction.
Only the owner of the workspace or a user with WM_ADMIN_ROLE can freeze a workspace. There are no specific privileges associated with freezing a workspace.
The LIVE workspace can be frozen only if freezemode is READ_ONLY or 1WRITER.
To reverse the effect of FreezeWorkspace, use the UnfreezeWorkspace procedure.
The following example freezes the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.FreezeWorkspace ('NEWWORKSPACE');
Returns the name of the workspace on which the session has performed the SetConflictWorkspace procedure.
DBMS_WM.GetConflictWorkspace() RETURN VARCHAR2;
None.
If the SetConflictWorkspace procedure has not been executed, the name of the current workspace is returned.
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
Returns the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions operation.
DBMS_WM.GetDiffVersions() RETURN VARCHAR2;
None.
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.
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)
Returns the locking mode, which determines whether or not access is enabled to versioned rows and corresponding rows in the previous version.
DBMS_WM.GetLockMode() RETURN VARCHAR2;
None.
This function returns E, S, C, or NULL.
E (exclusive), S (shared), and C (carry-forward), see the description of the lockmode parameter of the SetLockingON procedure.
NULL indicates that locking is not in effect. (Calling the SetLockingOFF procedure results in this setting.)
For an explanation of Workspace Manager locking, see Section 1.3. See also the descriptions of the SetLockingON and SetLockingOFF procedures.
The following example displays the locking mode in effect for the session.
SELECT DBMS_WM.GetLockMode FROM DUAL; GETLOCKMODE -------------------------------------------------------------------------------- C
Returns the names of workspaces visible in the multiworkspace views for version-enabled tables.
DBMS_WM.GetMultiWorkspaces() RETURN VARCHAR2;
None.
This procedure returns the names of workspaces visible in the multiworkspace views, which are described in Section 3.25.
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.
The following example displays the names of workspaces visible in the multiworkspace views.
SELECT DBMS_WM.GetMultiWorkspaces FROM DUAL;
Returns the context of the current operation.
DBMS_WM.GetOpContext() RETURN VARCHAR2;
None.
This function returns one of the following values:
DML: The current operation is driven by data manipulation language (DML) initiated by the user.
MERGE_REMOVE: The current operation was initiated by a MergeWorkspace procedure call with the remove_workspace parameter set to TRUE or a MergeTable procedure call with the remove_data parameter set to TRUE.
MERGE_NOREMOVE: The current operation was initiated by a MergeWorkspace procedure call with the remove_workspace parameter set to FALSE or a MergeTable procedure call with the remove_data parameter set to FALSE.
The following example displays the context of the current operation.
SELECT DBMS_WM.GetOpContext FROM DUAL; GETOPCONTEXT -------------------------------------------------------------------------------- DML
Returns a comma-separated list of all privileges that the current user has for the specified workspace.
DBMS_WM.GetPrivs( workspace VARCHAR2) RETURN VARCHAR2;
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace for which to return the list of privileges. The name is case sensitive. |
For information about Workspace Manager privileges, see Section 1.4.
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
Returns the current workspace for the session.
DBMS_WM.GetWorkspace() RETURN VARCHAR2;
None.
None.
The following example displays the current workspace for the session.
SELECT DBMS_WM.GetWorkspace FROM DUAL; GETWORKSPACE -------------------------------------------------------------------------------- B_focus_2
Goes to a point at or near the specified date and time in the current workspace.
DBMS_WM.GotoDate( in_date IN DATE);
| Parameter | Description |
|---|---|
in_date
|
Date and time for the read-only view of the workspace. (See the Usage Notes for details.) |
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 modifications, as set by the EnableVersioning procedure or modified by the SetWoOverwriteOFF or SetWoOverwriteON procedure:
NONE: The read-only view reflects the first savepoint after in_date.
VIEW_W_OVERWRITE: The read-only view contents can vary depending on when updates were performed and if or when savepoints were created. The view reflects the data values in effect at in_date except for rows that have been modified both (1) between in_date and the most recent savepoint before in_date and (2) between in_date and the next savepoint after in_date; for these rows the view reflects the date in effect at the most recent savepoint before in_date. Therefore, be careful if you use this procedure when the VIEW_W_OVERWRITE option is enabled.
VIEW_WO_OVERWRITE: The read-only view reflects the data values in effect at in_date.
For an explanation of the history options, see the description of the hist parameter for the EnableVersioning procedure. The following example scenario shows the effects of the VIEW_W_OVERWRITE and VIEW_WO_OVERWRITE settings. Assume the following sequence of events:
MANAGER_NAME value in a row is Adams.
SP1 is created.
MANAGER_NAME value is changed to Baxter.
in_date (in step 7) occurs.
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.)
SP2 is created.
in_date.
In the preceding scenario:
VIEW_W_OVERWRITE, the MANAGER_NAME value after step 7 is Adams.
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.
The following example goes to a point at or near midnight at the start of 30-Jun-2000, depending on the history option currently in effect.
EXECUTE DBMS_WM.GotoDate ('30-JUN-00');
Goes to the specified savepoint in the current workspace.
DBMS_WM.GotoSavePoint( [savepoint_name IN VARCHAR2 DEFAULT 'LATEST']);
| Parameter | Description |
|---|---|
savepoint_name
|
Name of the savepoint. The name is case sensitive. If |
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.2.2.
The following example goes to the savepoint named Savepoint1.
EXECUTE DBMS_WM.GotoSavepoint ('Savepoint1');
Moves the current session to the specified workspace.
DBMS_WM.GotoWorkspace( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
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:
workspace does not exist.
ACCESS_WORKSPACE privilege for workspace.
workspace has been frozen to new users (see the FreezeWorkspace procedure).
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');
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.
DBMS_WM.GrantSystemPriv( priv_types IN VARCHAR2, grantee IN VARCHAR2 [, grant_option IN VARCHAR2 DEFAULT 'NO'] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 |
grantee
|
Name of the user (can be the |
grant_option
|
Specify |
auto_commit
|
A Boolean value (
|
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:
grantee is not a valid user or role in the database.
priv_types.
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');
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.
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]);
| 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 |
workspace
|
Name of the workspace. The name is case sensitive. |
grantee
|
Name of the user (can be the |
grant_option
|
Specify |
auto_commit
|
A Boolean value (
|
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:
grantee is not a valid user or role in the database.
priv_types.
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');
Checks whether or not a workspace has any active sessions.
DBMS_WM.IsWorkspaceOccupied( workspace IN VARCHAR2) RETURN VARCHAR2;
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
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.
The following example checks if any sessions are active in the B_focus_2 workspace.
SELECT DBMS_WM.IsWorkspaceOccupied('B_focus_2') FROM DUAL; DBMS_WM.ISWORKSPACEOCCUPIED('B_FOCUS_2') -------------------------------------------------------------------------------- YES
Controls access to versioned rows in a specified table and to corresponding rows in the parent workspace.
DBMS_WM.LockRows( workspace IN VARCHAR2, table_name IN VARCHAR2 [, where_clause IN VARCHAR2 DEFAULT ''] [, lock_mode IN VARCHAR2 DEFAULT 'E']);
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.
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''');
Applies changes to a table (all rows or as specified in the WHERE clause) in a workspace to its parent workspace.
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]);
| 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
Only primary key columns can be specified in the
If |
create_savepoint
|
A Boolean value (
|
remove_data
|
A Boolean value (
|
auto_commit
|
A Boolean value (
|
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.2.5.)
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 regular 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.
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''');
Applies all changes in a workspace to its parent workspace, and optionally removes the workspace.
DBMS_WM.MergeWorkspace( workspace IN VARCHAR2 [, create_savepoint IN BOOLEAN DEFAULT FALSE] [, remove_workspace IN BOOLEAN DEFAULT FALSE] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
create_savepoint
|
A Boolean value (
|
remove_workspace
|
A Boolean value (
|
auto_commit
|
A Boolean value (
|
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.2.6.
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.2.5.)
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.2.1.)
An exception is raised if the user does not have the MERGE_WORKSPACE privilege for workspace or the MERGE_ANY_WORKSPACE privilege.
The following example merges changes in NEWWORKSPACE to its parent workspace and removes (by default) NEWWORKSPACE.
EXECUTE DBMS_WM.MergeWorkspace (`NEWWORKSPACE');
Applies to a workspace all changes made to a table (all rows or as specified in the WHERE clause) in its parent workspace.
DBMS_WM.RefreshTable( workspace IN VARCHAR2, table_id IN VARCHAR2 [, where_clause IN VARCHAR2 DEFAULT ''] [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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
Only primary key columns can be specified in the
If |
auto_commit
|
A Boolean value (
|
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.2.5.)
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 regular 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.
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''');
Applies to a workspace all changes made in its parent workspace.
DBMS_WM.RefreshWorkspace( workspace IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
auto_
commit
|
A Boolean value (
|
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.2.5.)
The specified workspace and the parent workspace are frozen in READ_ONLY mode, as explained in Section 1.2.6.
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.
The following example refreshes NEWWORKSPACE by applying changes made in its parent workspace.
EXECUTE DBMS_WM.RefreshWorkspace (`NEWWORKSPACE');
Rolls back the data in the workspace and removes all support structures created for the workspace. The workspace ceases to exist.
DBMS_WM.RemoveWorkspace( workspace IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
auto_
commit
|
A Boolean value (
|
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.2.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.
The following example removes the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.RemoveWorkspace('NEWWORKSPACE');
Removes the specified workspace and all its descendant workspaces. The data in the workspaces is rolled back and the workspace structure is removed.
DBMS_WM.RemoveWorkspaceTree( workspace IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
auto_
commit
|
A Boolean value (
|
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.2.1, a RemoveWorkspaceTree operation specifying Workspace 1 removes Workspace 1, Workspace 2, and Workspace 3. (For an explanation of database workspace hierarchy, see Section 1.2.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.
The following example removes the NEWWORKSPACE workspace and all its descendant workspaces.
EXECUTE DBMS_WM.RemoveWorkspaceTree('NEWWORKSPACE');
Resolves conflicts between workspaces.
DBMS_WM.ResolveConflicts( workspace IN VARCHAR2, table_name IN VARCHAR2, where_clause IN VARCHAR2, keep IN VARCHAR2);
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.2.5.)
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:
manager_name for Department 20 is changed in the LIVE database workspace from Tom to Mary.
manager_name for Department 20 is changed in Workspace1 from Tom to Franco.
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.
'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.
Workspace1 changes to the LIVE workspace.
For more information about conflict resolution, see Section 1.2.5.
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');
Revokes (removes) system-level privileges from users and roles.
DBMS_WM.RevokeSystemPriv( priv_types IN VARCHAR2, grantee IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 |
grantee
|
Name of the user (can be the |
auto_
commit
|
A Boolean value (
|
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:
grantee is not a valid user or role in the database.
priv_types.
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');
Revokes (removes) workspace-level privileges from users and roles for a specified workspace.
DBMS_WM.RevokeWorkspacePriv( priv_types IN VARCHAR2, workspace IN VARCHAR2, grantee IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 |
workspace
|
Name of the workspace. The name is case sensitive. |
grantee
|
Name of the user (can be the |
auto_
commit
|
A Boolean value (
|
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:
grantee is not a valid user or role in the database.
priv_types.
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');
Quits a conflict resolution session and discards all changes in the workspace since the BeginResolve procedure was executed.
DBMS_WM.RollbackResolve( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
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.2.6.
For more information about conflict resolution, see Section 1.2.5.
An exception is raised if one or more of the following apply:
workspace.
WM_ADMIN_ROLE role or that did not execute the BeginResolve procedure on workspace.
The following example quits the conflict resolution session in Workspace1 and discards all changes.
EXECUTE DBMS_WM.RollbackResolve ('Workspace1');
Discards all changes made in the workspace to a specified table (all rows or as specified in the WHERE clause).
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]);
| 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
Only primary key columns can be specified in the
If |
remove_locks
|
A Boolean value (
|
auto_commit
|
A Boolean value (
|
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.2.2, the user in Workspace 1 cannot roll back to savepoint SP1 until Workspace 3 (which caused implicit savepoint SPc to be created) is merged or removed.
An exception is raised if one or more of the following apply:
workspace does not exist.
workspace or any affected table.
table_id is active in workspace.
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');
Discards all changes made after a specified savepoint in the workspace to all tables.
DBMS_WM.RollbackToSP( workspace IN VARCHAR2, savepoint_name IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| 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 (
|
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.2.2, the user in Workspace 1 cannot roll back to savepoint SP1 until Workspace 3 (which caused implicit savepoint SPc to be created) is merged or removed.
An exception is raised if one or more of the following apply:
workspace does not exist.
savepoint_name does not exist.
workspace after savepoint_name, and the descendant workspaces that caused the implicit savepoints to be created still exist.
workspace or any affected table.
workspace.
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');
Discards all changes made in the workspace to all tables.
DBMS_WM.RollbackWorkspace( workspace IN VARCHAR2 [, auto_commit IN BOOLEAN DEFAULT TRUE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
auto_
commit
|
A Boolean value (
|
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.2.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.2.6.
An exception is raised if one or more of the following apply:
workspace has any descendant workspaces.
workspace does not exist.
workspace or any affected table.
workspace.
The following example rolls back any changes made in the NEWWORKSPACE workspace since that workspace was created.
EXECUTE DBMS_WM.RollbackWorkspace ('NEWWORKSPACE');
Determine whether or not conflicts exist between a workspace and its parent.
DBMS_WM.SetConflictWorkspace( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
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.21) 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 may have been changed in the workspace, use the SQL statement SELECT * FROM ALL_VERSIONED_TABLES. 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).
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');
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.
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);
| 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
If |
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 |
This procedure modifies the contents of the differences views (xxx_DIFF), which are described in Section 3.22. Each call to the procedure populates one or more sets of three rows, each set consisting of:
workspace1 (savepoint1 or LATEST savepoint values)
workspace2 (savepoint2 or LATEST savepoint values)
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.
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; MKT_ID MKT_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.22 explains how to interpret and use the information in the differences (xxx_DIFF) views.
Enables access to versioned rows and to corresponding rows in the parent workspace.
DBMS_WM.SetLockingOFF();
None.
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.
The following example sets locking off for the session.
EXECUTE DBMS_WM.SetLockingOFF;
Controls access to versioned rows and to corresponding rows in the previous version.
DBMS_WM.SetLockingON( lockmode IN VARCHAR2);
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:
C (carry-forward) unless another locking mode has been specified using the SetWorkspaceLockModeON procedure.
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.
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.
Makes the specified workspace or workspaces visible in the multiworkspace views for version-enabled tables.
DBMS_WM.SetMultiWorkspaces( workspaces IN VARCHAR2);
| Parameter | Description |
|---|---|
workspaces
|
The workspace or workspaces for which information is to be added to the multiworkspace views (described in Section 3.25). 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: |
This procedure adds rows to the multiworkspace views (xxx_MS). See Section 3.25 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:
workspaces.
workspaces is not valid.
workspaces.
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');
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).
DBMS_WM.SetWoOverwriteOFF();
None.
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.24. 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.
The following example disables the VIEW_WO_OVERWRITE history option.
EXECUTE DBMS_WM.SetWoOverwriteOFF;
Enables the VIEW_WO_OVERWRITE history option that had been disabled by the SetWoOverwriteOFF procedure.
DBMS_WM.SetWoOverwriteON();
None.
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.24. 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.
The following example enables the VIEW_WO_OVERWRITE history option.
EXECUTE DBMS_WM.SetWoOverwriteON;
Enables access to versioned rows in the specified workspace and to corresponding rows in the parent workspace.
DBMS_WM.SetWorkspaceLockModeOFF( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace for which to set the locking mode off. The name is case sensitive. |
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:
WM_ADMIN_ROLE role or is not the owner of workspace.
workspace.
workspace is a continually refreshed workspace (see the description of the isrefreshed parameter of the CreateWorkspace procedure).
The following example sets locking off for the workspace named NEWWORKSPACE.
EXECUTE DBMS_WM.SetWorkspaceLockModeOFF('NEWWORKSPACE');
Sets the default mode for the row-level locking in the workspace.
DBMS_WM.SetWorkspaceLockModeON( workspace IN VARCHAR2, lockmode IN VARCHAR2 [, override IN BOOLEAN DEFAULT FALSE]);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace for which to set the locking mode. The name is case sensitive. |
lockmode
|
Locking mode. Must be
|
override
|
A Boolean value (
|
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:
WM_ADMIN_ROLE role or is not the owner of workspace.
workspace.
workspace is a continually refreshed workspace (see the description of the isrefreshed parameter of the CreateWorkspace procedure).
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.
Enables changes to a workspace, reversing the effect of the FreezeWorkspace procedure.
DBMS_WM.UnfreezeWorkspace( workspace IN VARCHAR2);
| Parameter | Description |
|---|---|
workspace
|
Name of the workspace. The name is case sensitive. |
The operation fails if any sessions are active in workspace.
Only the owner of the workspace or a user with WM_ADMIN_ROLE can unfreeze a workspace. There are no specific privileges associated with freezing a workspace.
The following example unfreezes the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.FreezeWorkspace ('NEWWORKSPACE');
Enables access to versioned rows in a specified table and to corresponding rows in the parent workspace.
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']);
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).
The following example unlocks the EMPLOYEES table where last_name = 'Smith' in the NEWWORKSPACE workspace.
EXECUTE DBMS_WM.UnlockRows ('employees', 'NEWWORKSPACE', 'last_name = ''Smith''');
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|