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 |
|
The need to manage complex, long-duration database transactions is common in today's engineering applications, AM/FM, geographic information systems (GIS), and computer-aided design (CAD) applications.
Conventional database management systems (DBMS) are designed to handle short-duration transactions, such as those in financial applications. However, these techniques are insufficient for engineering applications where a design can take days to complete and multiple users must access the same database. Traditional concurrency control techniques are well-defined and adequate for handling short-duration transactions. These techniques are highly robust for handling very large numbers of short, distinct transactions that are open for milliseconds or minutes. A conventional short transaction locks all records that are changed until the transaction is completed (either merged or rolled back). These concurrency control techniques, however, are insufficient for handling applications that must support long-duration transactions.
Database Workspace Manager, often referred to as Workspace Manager, provides a long-transaction framework built on a workspace management system. It uses a series of short transactions and multiple data versions to implement a complete long-transaction event that maintains atomicity and concurrency. Changes are stored in the database as different workspaces. Users are permitted to create new versions of data to update, while maintaining a copy of the old data. The ongoing results of the long transaction are stored persistently, assuring concurrency and consistency.
This chapter explains the terminology used and also describes the workings of this product. You must understand this background information to use Workspace Manager procedures. This chapter has the following main sections:
For a complete example of Workspace Manager, see Section 1.9. However, you may want to read the rest of this chapter first, to understand the concepts that the example illustrates.
|
Note: Workspace Manager is installed by default in the Oracle seed database and any database created using the Database Configuration Assistant (DBCA). To use Workspace Manager in any other Oracle database, you must first perform the installation procedure described in Appendix A, "Installing Workspace Manager with Custom Databases". |
Workspace management refers to the ability of the database to hold different versions of the same record (that is, row) in one or more workspaces. Users of the database can then change these versions independently. There are two fundamental benefits of versioning in a database system:
The unit of versioning in the product is a database table. A table in the database can be version-enabled, which means that all rows in the table can now support multiple versions of data. The versioning infrastructure is not visible to the users of the database. After a table has been version-enabled, users automatically see the correct version of the record in which they are interested.
A workspace is a virtual environment that one or more users can share to make changes to the data in the database. Workspace management involves managing one or more workspaces that can be shared by many users.
There can be one or more versions of a row in a workspace from one or more version-enabled tables. The current or active version in a workspace refers to the version number in which the changes are currently being made. All changes made in a workspace are made by conventional short transactions. There can be a hierarchy of workspaces in the database. For example, a workspace can be a parent to one or more workspaces. By default, when a workspace is created, it is created from the topmost, or LIVE, database workspace. (Workspace names are case sensitive, and the workspace name of the live database is spelled LIVE. The length of a workspace name must not exceed 30 characters.) Users are included in workspaces by a GotoWorkspace operation.
Figure 1-1 shows a hierarchy of workspaces. Workspace 1 and Workspace 4 were formed off the LIVE database workspace; Workspace 2 and Workspace 3 were formed off Workspace 1, and Workspace 5 was formed off Workspace 4. After Workspace 1 was created, a user executed a GotoWorkspace operation specifying Workspace 1, and then executed CreateWorkspace operations to create Workspace 2 and Workspace 3. A comparable sequence was followed with Workspace 4 and Workspace 5.
A savepoint is a point in the workspace to which operations can be rolled back. It is analogous to a firewall, in that by creating a savepoint you can prevent any damage to the "other side" of the wall (that is, operations performed in the workspace before the savepoint was created).
Explicit savepoints can thus be created and later used to effect partial rollbacks in workspaces. In Figure 1-2, SP1, SP2, SP3 and SP4 are explicit savepoints that have been created in the workspaces indicated. (Savepoints are indicated by dashed lines in Figure 1-2.)
In addition, implicit savepoints are created automatically whenever a new workspace is created. Thus, in Figure 1-2 two implicit savepoints (SPa and SPd) are created in the LIVE workspace corresponding to Workspace 1 and Workspace 4 creation; two implicit savepoints (SPb and SPc) are created in Workspace 1 corresponding to Workspace 2 and Workspace 3 creation; and one implicit savepoint (SPe) is created in Workspace 4 corresponding to Workspace 5 creation. An implicit savepoint is needed so that the users in the child workspace get a view of the database that is frozen at the time of the workspace creation.
Workspace Manager uses the name LATEST to designate a logical savepoint that refers to the latest version in the workspace. LATEST is often the default when a savepoint is an optional parameter for a procedure.
Workspaces can be merged or rolled back.
Merging a workspace involves applying changes made in a workspace to its parent workspace, after which the workspace that had been merged ceases to exist (that is, it is removed). To merge a workspace, use the MergeWorkspace procedure.
Rolling back a workspace involves deleting either all changes made in the workspace or all changes made after a savepoint (that is, an explicit savepoint).
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 descendent workspaces that caused the implicit savepoints to be created. For example, referring to Figure 1-2 in Section 1.2.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.
Note:
A workspace cannot be rolled back when it has open regular transactions. Rollback of a workspace leaves behind the workspace structure for future use; only the data in the workspace is deleted. (To completely remove a workspace, use the RemoveWorkspace procedure, as described in Section 1.2.7.)
Many Workspace Manager procedures by default commit the caller's current open regular transaction, if there is one. For example, if you compress a workspace or delete a savepoint, and if a regular transaction is open, that transaction is automatically committed when the requested Workspace Manager operation completes, unless you specify otherwise.
To override the automatic commit operation in such cases, you can specify the auto_commit parameter with a value of FALSE for many procedures. The auto_commit parameter has a default value of TRUE; however, if you specify a value of FALSE, any currently open regular transaction remains open, and no operations (including the Workspace Manager procedure) are committed until the transaction is committed.
Specifying FALSE for the auto_commit parameter is useful if you want to perform transactional operations unrelated to Workspace Manager before and after calling a Workspace Manager procedure, without having the Workspace Manager procedure terminate the transaction and make permanent the changes. If you later decide to roll back the transaction, all the operations (including the Workspace Manager procedure) are rolled back. Note that if you specify FALSE for the auto_commit parameter, you must remember to commit or roll back the transaction.
On a merge, the changes in the workspace are incorporated in its parent workspace. Rows that are changed in the child and parent workspace may lead to data conflicts. Conflicts are detected at merge time and presented to the user in conflict views. There is one conflict view per table, as described in Section 3.21. This view lists the primary key of the rows in conflict and also the column values of the rows in the two workspaces that form the conflict.
Conflicts have to be resolved manually by using the ResolveConflicts procedure. When there are no conflicts between the parent and child workspaces, the data in the two workspaces can be merged. Conflicts must be resolved before a MergeWorkspace or RefreshWorkspace operation can be performed.
Typically, you discover that conflicts exist when you attempt to merge or refresh a workspace and encounter an exception that refers to conflicts. The general process for resolving conflicts is as follows:
A workspace can be frozen or not frozen. If a workspace is frozen, no changes can be made to data in version-enabled rows, and access to the workspace is restricted.
To make a workspace frozen, use the FreezeWorkspace procedure. To make a frozen workspace not frozen, use the UnfreezeWorkspace procedure.
In addition, some procedures automatically freeze one or more workspaces. Table 1-1 lists these procedures, the workspaces affected, and the mode in which the workspaces are frozen. (For explanations of the mode values, see the FreezeWorkspace procedure description in Chapter 2.)
A workspace can be removed with the RemoveWorkspace procedure. RemoveWorkspace rolls back the data in a workspace and then deletes the workspace structure. An entire tree of workspaces can be removed with the RemoveWorkspaceTree procedure. This will remove the workspace and all its descendant workspaces. A workspace cannot be removed when it has users in it.
In addition to locks provided by conventional Oracle short transactions, Workspace Manager provides two types of version locks. These locks are primarily intended to eliminate row conflicts between a parent workspace and a child workspace. Locking is enabled at a session level and is a session property independent of the workspace that the session is in. When locking is enabled for a session, it locks rows in all workspaces in which it participates. The two types of version locks are:
Locks persist for the duration of the workspace. Merge or rollback of the workspace removes the locks.
The xxx_LOCK metadata views (described in Section 3.23) contain information about locks in each version-enabled table.
Workspace Manager has a set of privileges that are separate from standard Oracle database privileges. Workspace Manager workspace-level privileges (with names in the form xxx_WORKSPACE) allow the user to affect a specified workspace, and system-level privileges (with names in the form xxx_ANY_WORKSPACE) allow the user to affect any workspace.
Table 1-2 lists the Workspace Manager privileges.
Each privilege can be granted with or without the grant option. The grant option allows the user to which the privilege is granted to grant the privilege to other users.
The WM_ADMIN_ROLE role has all Workspace Manager privileges with the grant option. By default, the database administrator (DBA role) is granted the WM_ADMIN_ROLE role. Thus, after you decide which users should be granted which privileges, either have the DBA grant the privileges, or have the DBA grant the WM_ADMIN_ROLE role to one or more selected users and have these users grant the privileges.
The GrantWorkspacePriv and GrantSystemPriv procedures are used to grant workspace-level privileges and system-level privileges, respectively.
The RevokeWorkspacePriv and RevokeSystemPriv procedures are used to revoke workspace-level privileges and system-level privileges, respectively. These procedures require that the user have sufficient privilege to revoke the specified privilege from the specified user. The user that granted a privilege can revoke it.
Standard Oracle database import and export operations can be performed on version-enabled databases; however, the following considerations and restrictions apply:
LIVE workspace).
IGNORE=Y.
FROMUSER and TOUSER capabilities of the Oracle9i Import utility are not supported with version-enabled databases.
Version-enabled tables can have referential integrity constraints, including constraints with the CASCADE and RESTRICT options; however, the following considerations and restrictions apply:
EMPLOYEE and DEAPRTMENT table definitions, with a foreign key constraint added after the creation (that is, the DEPT_ID in each EMPLOYEE row must match an existing DEPT_ID in a DEPARTMENT row).
CREATE TABLE employee ( employee_id NUMBER, last_name VARCHAR2(32), first_name VARCHAR2(32), dept_id NUMBER); CREATE TABLE department ( dept_id NUMBER, name VARCHAR2(32); ALTER TABLE employee ADD CONSTRAINT emp_forkey_deptid FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON DELETE CASCADE;
DEPARTMENT is considered the parent and EMPLOYEE is considered the child in the referential integrity relationship in this example; and if DEPARTMENT is version-enabled, EMPLOYEE must be version-enabled also. In this relationship definition, when a DEPARTMENT row is deleted, all its child rows in the EMPLOYEE table are deleted (cascading delete operation).
Workspace Manager uses the ALL_WM_RIC_INFO and USER_WM_RIC_INFO views (described in Chapter 3) to hold information pertinent to referential integrity support.
Version-enabled tables can have triggers defined; however, the following considerations and restrictions apply:
action_type must be PL/SQL.
Any triggers that are not supported for version-enabled tables are deactivated when versioning is enabled, and are activated when versioning is disabled.
Workspace Manager provides PL/SQL procedures that you call in order to use the product's features. The procedures are in a single PL/SQL package, but they can be logically grouped into the following categories described in this section.
|
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). |
Reference information for all interfaces is in Chapter 2.
Table management procedures enable and disable workspace management on a table.
Table 1-3 shows the procedures available for table management.
| Procedure | Description |
|---|---|
|
Enables a table for workspace management. |
|
|
Removes workspace management capabilities from a table. |
Workspace management procedures perform operations on workspaces.
Table 1-4 shows the procedures available for workspace management.
| Procedure | Description |
|---|---|
|
Creates a new workspace in the database. |
|
|
Adds the user to the specified workspace. |
|
|
Finds differences in values in version-enabled tables for two savepoints and their common ancestor (base). It creates rows in the differences views describing these differences. |
|
|
Returns the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions operation. |
|
|
Applies changes to a table (all rows or as specified in the |
|
|
Applies all changes in a workspace to its parent workspace, and optionally removes the workspace. |
|
|
Discards all changes made in the workspace since the last merge. |
|
|
Returns the workspace to a specified savepoint. |
|
|
Applies to a workspace all changes made to a table (all rows or as specified in the |
|
|
Applies to a workspace all changes made in its parent workspace. |
|
|
Modifies the description of a workspace. |
|
|
Rolls back and deletes a workspace. |
|
|
Deletes a workspace and its descendant workspaces. |
|
|
Disables changes to a workspace. |
|
|
Enables changes to a workspace after it has been frozen. |
|
|
Deletes explicit savepoints (all or some) in a workspace, and minimizes the Workspace Manager metadata structures. |
|
|
Deletes explicit savepoints in a workspace and all its descendant workspaces, and minimizes the Workspace Manager metadata structures for the affected workspaces. |
|
|
Checks whether or not a workspace has any active sessions. |
|
|
Returns the current workspace for the session. |
Savepoint management procedures perform operations related to savepoints.
Table 1-5 shows the procedures available for savepoint management.
Privilege management procedures grant and revoke Workspace Manager privileges.
Table 1-6 shows the procedures available for privilege management.
Lock management procedures control Workspace Manager locking.
Table 1-7 shows the procedures available for lock management.
Conflict management procedures detect and resolve conflicts between workspaces.
Table 1-8 shows the procedures available for conflict management.
| Procedure | Description |
|---|---|
|
Determines the conflicts between a workspace and its parent. |
|
|
Returns the name of the workspace on which the session has performed the SetConflictWorkspace procedure. |
|
|
Starts a conflict resolution session. |
|
|
Resolves conflicts between workspaces. |
|
|
Ends a conflict resolution session and saves changes. |
|
|
Quits a conflict resolution session and does not save changes. |
|
|
Makes the specified workspace or workspaces visible in the multiworkspace views for version-enabled tables. |
|
|
Returns the names of workspaces visible in the multiworkspace views for version-enabled tables. |
This section presents a simplified example of using Workspace Manager to try out some scenarios. It refers to concepts that were explained in this chapter, and it uses procedures documented in Chapter 2.
In Example 1-1, a soft drink (cola) manufacturer has four products, each with a marketing manager and a marketing budget. Because of an exceptional opportunity for growth in one (cola_b) market, the company wants to do "what-if" analyses involving different managers and budget amounts.
------------------------------------------------------------------- -- INITIAL SET-UP ------------------------------------------------------------------- -- Create the user for schema objects. CREATE USER wm_developer IDENTIFIED BY wm_developer; -- Grant regular privileges. GRANT connect, resource to wm_developer; GRANT create table to wm_developer; -- Grant WM-specific privileges (with grant_option = YES). EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE', 'wm_developer', 'YES'); --------------------------------------------------------------------------- -- CREATE AND POPULATE DATA TABLE -- --------------------------------------------------------------------------- CONNECT wm_developer/wm_developer -- Cleanup: remove B_focus_2 workspace if it exists from previous run. EXECUTE DBMS_WM.RemoveWorkspace ('B_focus_2'); -- Create a table for the annual marketing budget for -- several cola (soft drink) markets in a -- given geography (such as city or state). -- Each row will contain budget data for a specific -- cola. Note: This table does not reflect recommended -- database design. (For example, a manager ID should -- be used, not a name.) It is deliberately oversimplified -- for purposes of illustration. CREATE TABLE cola_marketing_budget ( mkt_id NUMBER PRIMARY KEY, mkt_name VARCHAR2(32), manager VARCHAR2(32), -- Here a name, just for simplicity budget NUMBER -- Budget in millions of dollars. Example: 3 = $3,000,000. ); -- Version-enable the table. Specify hist option of VIEW_WO_OVERWRITE so that -- the COLA_MARKETING_BUDGET_HIST view contains complete history information. EXECUTE DBMS_WM.EnableVersioning ('cola_marketing_budget', 'VIEW_WO_OVERWRITE'); INSERT INTO cola_marketing_budget VALUES( 1, 'cola_a', 'Alvarez', 2.0 ); INSERT INTO cola_marketing_budget VALUES( 2, 'cola_b', 'Baker', 1.5 ); INSERT INTO cola_marketing_budget VALUES( 3, 'cola_c', 'Chen', 1.5 ); INSERT INTO cola_marketing_budget VALUES( 4, 'cola_d', 'Davis', 3.5 ); COMMIT; -- Relevant data values now in LIVE workspace: -- cola_a, Alvarez, 2.0 -- cola_b, Baker, 1.5 -- cola_c, Chen, 1.5 -- cola_d, Davis, 3.5 --------------------------------------------------------------------------- -- CREATE WORKSPACES -- --------------------------------------------------------------------------- -- Create workspaces for the following scenario: a major marketing focus -- in the cola_b area. Managers and budget amounts for each -- market can change, but the total marketing budget cannot grow. -- -- One scenario (B_focus_1) features a manager with more expensive -- plans (which means more money taken from other areas' budgets). -- The other scenario (B_focus_2) features a manager with less expensive -- plans (which means less money taken from other areas' budgets). -- -- Two workspaces (B_focus_1 and B_focus_2) are created as child workspaces -- of the LIVE database workspace. EXECUTE DBMS_WM.CreateWorkspace ('B_focus_1'); EXECUTE DBMS_WM.CreateWorkspace ('B_focus_2'); --------------------------------------------------------------------------- -- WORK IN FIRST WORKSPACE -- --------------------------------------------------------------------------- -- Enter the B_focus_1 workspace and change the cola_b manager to Beasley and -- raise the cola_b budget amount by 1.5 to bring it to 3.0. Reduce all other -- area budget amounts by 0.5 to stay within the overall budget. EXECUTE DBMS_WM.GotoWorkspace ('B_focus_1'); UPDATE cola_marketing_budget SET manager = 'Beasley' WHERE mkt_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 3 WHERE mkt_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 1.5 WHERE mkt_name = 'cola_a'; UPDATE cola_marketing_budget SET budget = 1 WHERE mkt_name = 'cola_c'; UPDATE cola_marketing_budget SET budget = 3 WHERE mkt_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_1 workspace:: -- cola_a, Alvarez, 1.5 -- cola_b, Beasley, 3.0 -- cola_c, Chen, 1.0 -- cola_d, Davis, 3.0 -- Freeze this workspace. (Prevent any changes until workspace is unfrozen.) -- However, first go to the LIVE workspace, because a workspace cannot be -- frozen if any users (including you) are in it. EXECUTE DBMS_WM.GotoWorkspace ('LIVE'); EXECUTE DBMS_WM.FreezeWorkspace ('B_focus_1'); --------------------------------------------------------------------------- -- WORK IN SECOND WORKSPACE -- --------------------------------------------------------------------------- -- Enter the B_focus_2 workspace and change the cola_b manager to Burton and -- raise the cola_b budget amount by 0.5 to bring it to 2.0. Reduce only the -- cola_d amount by 0.5 to stay within the overall budget. EXECUTE DBMS_WM.GotoWorkspace ('B_focus_2'); UPDATE cola_marketing_budget SET manager = 'Burton' WHERE mkt_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 2 WHERE mkt_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 3 WHERE mkt_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_2 workspace:: -- cola_a, Alvarez, 2.0 (no change from LIVE) -- cola_b, Burton, 2.0 -- cola_c, Chen, 1.5 (no change from LIVE) -- cola_d, Davis, 3.0 (same manager, new budget) -- Create a savepoint (B_focus_2_SP1), then change scenario to -- raise cola_b budget and reduce cola_d budget by 0.5 each. EXECUTE DBMS_WM.CreateSavepoint ('B_focus_2', 'B_focus_2_SP1'); UPDATE cola_marketing_budget SET budget = 2.5 WHERE mkt_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 2.5 WHERE mkt_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_2 workspace: -- cola_a, Alvarez, 2.0 (no change from LIVE) -- cola_b, Burton, 2.5 -- cola_c, Chen, 1.5 (no change from LIVE) -- cola_d, Davis, 2.5 (same manager, new budget) -- Discard this scenario; rollback to row values at the time savepoint -- B_focus_2_SP1 was created. First, though, get out of the workspace -- so it can be rolled back (no users in it). EXECUTE DBMS_WM.GotoWorkspace ('LIVE'); EXECUTE DBMS_WM.RollbackToSP ('B_focus_2', 'B_focus_2_SP1'); -- Go back to the B_focus_2 workspace and display current values -- (should include budget of 2 for cola_b and 3 for cola_d). SELECT * FROM cola_marketing_budget; --------------------------------------------------------------------------- -- SELECT SCENARIO AND UPDATE DATABASE -- --------------------------------------------------------------------------- -- Assume that you have decided to adopt the scenario of the second -- workspace (B_focus_2) using that workspace's current values. -- First go to the LIVE workspace, because the other cannot be removed -- or merged if any users (including you) are in it. EXECUTE DBMS_WM.GotoWorkspace ('LIVE'); -- Unfreeze the first workspace and remove it to discard any changes there. EXECUTE DBMS_WM.UnfreezeWorkspace ('B_focus_1'); EXECUTE DBMS_WM.RemoveWorkspace ('B_focus_1'); -- Apply changes in the second workspace to the LIVE database workspace. -- Note that the workspace is removed by default after MergeWorkspace. EXECUTE DBMS_WM.MergeWorkspace ('B_focus_2'); -- Display the current data values (which are in the LIVE database -- workspace, which is the only workspace currently existing). SELECT * FROM cola_marketing_budget; --------------------------------------------------------------------------- -- DISABLE VERSIONING -- --------------------------------------------------------------------------- -- Disable versioning on the table because you are finished testing scenarios. -- Also, users with version enabled tables cannot be dropped, in case you -- want to drop the wm_developer user. -- Set force parameter to TRUE if you want to force the disabling even -- if changes were made in a non-LIVE workspace. EXECUTE DBMS_WM.DisableVersioning ('cola_marketing_budget', TRUE);
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|