Release 2 (9.2)
Part Number A96628-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Workspace Manager Release 2 (9.2) Part Number A96628-01 |
|
Oracle Workspace Manager, often referred to as Workspace Manager, provides an infrastructure that lets applications conveniently create workspaces and group different versions of table row values in 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 activity are stored persistently, assuring concurrency and consistency.
Applications that can benefit from Workspace Manager typically do one or more of the following operations:
Workspace Manager lets you review changes and roll back undesirable ones before making the changes public. Until you make the changes public, they are invisible to other users of the database, who will access only the regular production data. You can organize the changes in a simple set of workspaces or in a complex workspace hierarchy. A typical example might be a life sciences application in which Workspace Manager supports the discovery and quality assurance (QA) processes by managing a collection of updates before they are merged with the production data.
Workspace Manager lets a team share access to a collection of updates and insertions for a collaborative project. Workspace privileges control access to a workspace and its operations, and you can restrict workspace access to single-writer, read-only, or no access. Workspace locks prevent update conflicts between projects in separate workspaces. A typical example might be an application to design an engineering project, in which multiple subprojects are concurrently developed in separate workspaces.
Workspace Manager lets you organize changes in workspaces to view them in the context of the whole database, but without requiring that you actually copy data between tables. It lets different users make simultaneous changes to the same row, and it lets you detect and resolve conflicts. A typical example might be a telecommunications application that lets you create multiple cell phone coverage scenarios to find the optimal design.
Workspace Manager lets you navigate workspaces and row versions to view the database as of a particular milestone or point in time. You can roll back changes to a row or table in a workspace to a milestone. A typical example might be a land information management application where Workspace Manager supports regulatory requirements by maintaining a history of all changes to land parcels.
Workspace Manager has also proven to be useful in managing "long transaction" scenarios, where complex, long-duration database transactions can take days to complete and multiple users must access the same database.
This chapter explains concepts and operations that you must understand to use Workspace Manager. It has the following main sections:
For a complete example of Workspace Manager, see Section 1.11. 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 Manager lets you version-enable one or more user tables in the database. When a table is version-enabled, all rows in the table can support multiple versions of the data. The versioning infrastructure is not visible to the users of the database, and application SQL statements for selecting, inserting, modifying, and deleting data continue to work in the usual way with version-enabled tables. (Workspace Manager implements these capabilities by maintaining system views and creating INSTEAD OF triggers, as explained in Section 1.1.8; however, application developers and users do not need to see or interact with the views and triggers.)
After a table is version-enabled, users in a workspace automatically see the correct version of the record in which they are interested. If you no longer need a table to be version-enabled, you can disable versioning for the table.
A workspace is a virtual environment that one or more users can share to make changes to the data in the database. A workspace logically groups collections of new row versions from one or more version-enabled tables, and isolates these versions until they are explicitly merged with production data or discarded, thus providing maximum concurrency. Users can perform a variety of operations involving workspaces: go to, create, refresh, merge, roll back, remove, compress, alter, and other operations.
Users in a workspace always see a transactionally consistent view of the entire database; that is, they see changes made in their current workspace plus the rest of the data in the database as it existed either when the workspace was created or when the workspace was most recently refreshed with changes from the parent workspace. (Workspace hierarchy and parent and child workspaces are explained in Section 1.1.1.)
Workspace Manager automatically detects conflicts, which are differences in data values resulting from changes to the same row in a workspace and its parent workspace. You must resolve conflicts before merging changes from a workspace into its parent workspace. You can use workspace locks to avoid conflicts.
Savepoints are points in the workspace to which row changes in version-enabled tables can be rolled back, and to which users can go to see the database as it existed at that point. Savepoints are usually created in response to a business-related milestone, such as the completion of a design phase or the end of a billing period. (For more information about savepoints, see Section 1.1.2.)
The history option lets you timestamp changes made to all rows in a version-enabled table and to save a copy of either all changes or only the most recent changes to each row. If you keep all changes (specifying the "without overwrite" history option) when version-enabling a table, you keep a persistent history of all changes made to all row versions, and enable users to go to any point in time to view the database as it existed from the perspective of that workspace.
Workspace Manager provides a comprehensive PL/SQL API that you can add to new and existing applications to manage workspaces, savepoints, history information, privileges, access modes, and Workspace Manager locks, and to detect and resolve conflicts. You can also perform many of these operations using the Oracle Enterprise Manager graphical user interface.
Another database object created by Workspace Manager is a database-wide system table that maps row versions to workspaces. This table is not visible to users.
There can be a hierarchy of workspaces in the database. For example, a workspace can be a parent to one or more workspaces (child 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 a workspace by a GotoWorkspace operation.
Figure 1-1 shows a hierarchy of workspaces. Workspace1 and Workspace4 were formed off the LIVE database workspace; Workspace2 and Workspace3 were formed off Workspace1, and Workspace5 was formed off Workspace4. After Workspace1 was created, a user executed a GotoWorkspace operation specifying Workspace1, and then executed CreateWorkspace operations to create Workspace2 and Workspace3. A comparable sequence was followed with Workspace4 and Workspace5.
See also Section 1.1.2.1 for a discussion of design issues in deciding whether to create a child workspace or a savepoint for certain needs
A savepoint is a point in the workspace to which data changes can be rolled back. Workspace Manager accomplishes the rollback by deleting the row versions that contain the unwanted changes.
An explicit savepoint is a savepoint that you create and name. You can later roll back changes in version-enabled tables to the savepoint, or you can go to the savepoint to view the state of the entire database (including versioned rows) at the time the savepoint was created. 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. 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. Thus, in Figure 1-2 two implicit savepoints (SPa and SPd) are created in the LIVE workspace corresponding to Workspace1 and Workspace4 creation; two implicit savepoints (SPb and SPc) are created in Workspace1 corresponding to Workspace2 and Workspace3 creation; and one implicit savepoint (SPe) is created in Workspace4 corresponding to Workspace5 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.
A removable savepoint is a savepoint that can be deleted by the CompressWorkspace, CompressWorkspaceTree, and DeleteSavepoint procedures. A savepoint is removable if either of the following applies:
A Workspace Manager design issue that you may face is whether to create a savepoint or a child workspace to "save" a project at a given point. Both a savepoint and a child workspace allow you to group a set of changes, compare changes in different row versions, and roll back a set of changes. However, creating a savepoint lets you continue to make changes in the same workspace, and it allows other users in the workspace immediate access to the changes. (Changes in another workspace are not visible to users until the current workspace is refreshed or merged.) Creating a savepoint also makes it convenient to archive a set of changes, to which you can later roll back.
On the other hand, creating a child workspace is convenient for providing an isolated environment in which a complex set of changes can be made, completely removed from the parent workspace (for example, the production data). If you want to set up an independent environment for a scenario, and if regular users in the parent workspace do not need access to this scenario's data, you probably want to create a child workspace instead of simply creating a savepoint in the parent workspace.
Workspaces can be merged or rolled back.
Merging a workspace involves applying changes made in a child workspace to its parent workspace, after which the child workspace is removed. To merge a workspace, use the MergeWorkspace procedure.
Rolling back a workspace involves deleting either all data changes (row versions) made in the workspace or all changes made after an explicit savepoint.
|
Note: 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.1.2, the user in |
A workspace cannot be rolled back when it has open database 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.1.6.)
When a child workspace is merged, the row changes in the child workspace are incorporated in its parent workspace; and when a child workspace is refreshed, row changes in the parent workspace are incorporated in the child workspace. When a row is changed in both the child and parent workspace, a data conflict is created. Conflicts are automatically detected when a merge or refresh operation is requested, and they are presented to the user in conflict views. There is one conflict view per table, as described in Section 3.25. This view lists the column values of the rows in the two workspaces involved in the conflict.
Conflicts must be resolved manually using the ResolveConflicts procedure. For each conflict you can choose to keep the row from the child workspace, the row from the parent workspace, or the common base row (that is, no change: keep the original data values for the row). You must resolve the conflicts before you can perform a merge (MergeWorkspace) or refresh (RefreshWorkspace) operation. The general process for resolving conflicts is as follows:
You can control read and write access to a workspace by freezing and unfreezing the workspace. If a workspace is frozen, the ability of users to access the workspace and to make changes to rows in version-enabled tables is restricted. You can freeze a workspace in any of the following modes: no access, read-only, and one writer only (1WRITER).
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.
Many Workspace Manager operations are by default executed as autonomous database transactions that will be committed when they finish. That is, each such transaction is an independent transaction that is called from within the current database transaction, leaves the context of the calling transaction, performs the Workspace Manager operation and then automatically commits it, and then returns to the calling transaction's context and continues with that transaction. Workspace Manager procedures that operate in this way have an optional auto_commit parameter, which has a default value of TRUE.
For example, the CompressWorkspace procedure by default starts an autonomous transaction, compresses the workspace, commits the compression operation, and returns to the calling transaction's context, where the current database transaction continues.
However, if you want such procedures not to start an autonomous transaction, but instead to execute in the context of the calling transaction, you can specify the auto_commit parameter with a value of FALSE. In this case, the Workspace Manager operation is executed as part of the current database transaction; and if there is no current open transaction, the Workspace Manager operation starts a new transaction. In either case, the Workspace Manager operation does not take effect until that transaction ends with a commit operation. For example, if you call the CompressWorkspace procedure with the auto_commit parameter specified as FALSE, the workspace is not compressed until the transaction is committed; and if the transaction is rolled back, the workspace is not compressed.
Note that if you specify FALSE for the auto_commit parameter, you must remember to commit or roll back the transaction explicitly.
When you version-enable a table using the EnableVersioning procedure, Workspace Manager automatically performs operations and creates data structures that are invisible to non-DBA users, but that permit Workspace Manager to function. Some of the information maintained by Workspace Manager is stored in the metadata views described in Chapter 3, and some is stored in system data structures not accessible by users.
When a table is version-enabled, Workspace Manager renames the table to <table-name>_LT, and it adds several columns to this table to store versioning metadata. Note that users and applications should never specify the <table-name>_LT table in SQL statements; they should continue to specify the original table name (<table-name>).
Workspace Manager also creates a view on the original table (<table-name>), as well as INSTEAD OF triggers on the view for insert, update, and delete operations. When an application executes a statement to insert, update, or delete data in a version-enabled table, the appropriate INSTEAD OF trigger performs the actual operation. When the view is accessed, it uses the workspace metadata to show only the row versions relevant to the current workspace of the user.
Workspace Manager creates a user named WMSYS. The WMSYS schema is used to store all the metadata information for Workspace Manager. A PL/SQL package with the public synonym DBMS_WM contains the Workspace Manager procedures.
The following privileges are granted to the PUBLIC user group:
SELECT privilege on Workspace Manager metadata views (described in Chapter 3)EXECUTE privilege on the DBMS_WM package (described in Chapter 2)Users perform Workspace Manager operations within a standard Oracle session. (A session is a specific connection of a user to an Oracle instance through a user process; a session lasts from the time the user connects until the time the user disconnects or exits the database application.) When you perform Workspace Manager operations, information relating to the session context is automatically recorded.
The session context information includes the workspace name and a context value, and it determines what data the session can see in the workspace and what workspaces the session can enter. The context value is one of the following:
LATEST: The session is currently set to the LATEST savepoint (explained in Section 1.1.2), and it can see changes as they are made in the workspace. The context is automatically set to LATEST when the session enters the workspace (using the GotoWorkspace procedure).You can retrieve information about the session context by using the GetSessionInfo procedure. Retrieving this information can be 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.
In addition to locks provided by regular Oracle database 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. You can enable locking for the workspace, the session, or specified rows, or some combination:
WHERE clause after the update).Workspace or session locks persist for the duration of the workspace or session, respectively, or until the workspace is merged or rolled back.
Like database locks, Workspace Manager locks can be exclusive or shared:
The xxx_LOCK metadata views (described in Section 3.27) contain information about locks in each version-enabled table.
Workspace Manager provides 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.To perform DDL (data definition language) operations on a version-enabled table, you must use special Workspace Manager procedures before and after the DDL operations, and you must specify the name of a special table created by Workspace Manager. You cannot perform DDL operations in the usual manner on the table or any index or trigger that refers to the table. For example, to add a column to a table named EMPLOYEES that has been version-enabled, you cannot simply enter a statement in the form ALTER TABLE EMPLOYEES ADD (column-name data-type).
The reason for these requirements is to ensure that Workspace Manager versioning metadata is updated to reflect the DDL changes. Therefore, DDL operations affecting a version-enabled table must be preceded by a call to the BeginDDL procedure, and must be concluded by a call to either the CommitDDL or RollbackDDL procedure. The BeginDDL procedure creates an empty temporary table with a name in the form <table-name>_LTS (the S standing for skeleton). The actual DDL statement must specify the name of the temporary <table-name>_LTS table, and must not specify the <table-name> or <table-name>_LT name. The CommitDDL and RollbackDDL procedures delete the temporary <table-name>_LTS table.
The following DDL operations related to version-enabled tables are supported:
ADD, DROP, MODIFY (but for MODIFY only the following operations: changing the default value of a column; changing the data type of a column that contains only null values or for which there are no existing data rows)CREATE INDEX, DROP INDEX, ALTER INDEX (but for ALTER INDEX only the following options: logging, pctfree, initrans, maxtrans, initialextent, minextents, nextextent, maxextents, pctincrease, freelists, freelist groups, and buffer_pool)CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER ENABLE/DISABLEIf you try to perform an unsupported DDL operation, the change will not be made, and an exception might be raised by the CommitDDL procedure.
If the DDL operation involving a version-enabled table is on an index (for example, creating an index on the table), you must have the CREATE TABLE privilege.
If you need to perform DDL operations on a version-enabled table in an Oracle replication environment, see Section C.3 for additional guidelines.
Example 1-1 shows the statements needed to add a column named COMMENTS to the COLA_MARKETING_BUDGET table by using the special table named COLA_MARKETING_BUDGET_LTS. It also includes a DESCRIBE statement to show the addition of the column.
EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET'); ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100)); DESCRIBE cola_marketing_budget_lts; Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT_ID NOT NULL NUMBER PRODUCT_NAME VARCHAR2(32) MANAGER VARCHAR2(32) BUDGET NUMBER COMMENTS VARCHAR2(100) EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');
In Example 1-1, the ALTER TABLE statement specifies the COLA_MARKETING_BUDGET_LTS table, which is created by the BeginDDL procedure. The CommitDDL procedure applies the change to the COLA_MARKETING_BUDGET table and deletes the COLA_MARKETING_BUDGET_LTS table.
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 DEPARTMENT table definitions, with a foreign key constraint added after the creation (that is, the dept_id value in each EMPLOYEE row must match an existing dept_id value 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;
In this example, DEPARTMENT is considered the parent and EMPLOYEE is considered the child in the referential integrity relationship; 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).
EMPLOYEE(emp_id, dept_id) could have the constraint that the department ID must exist in the table DEPARTMENT(dept_id, dept_name, loc_id); and the table DEPARTMENT(dept_id, dept_name, loc_id) could have the constraint that the location ID must exist in the table LOCATION(loc_id, loc_name). However, all tables that are involved in multilevel referential integrity constraints must be version-enabled and version-disabled together, unless all the referential integrity constraints involved have the Restrict rule. If all the constraints involved have the Restrict rule, you can version-enable the tables either all together or one at a time with child tables preceding their parent tables. The table names must be passed as a comma-delimited list to the EnableVersioning and DisableVersioning procedures.Workspace Manager uses the metadata views ALL_WM_RIC_INFO and USER_WM_RIC_INFO (described in Chapter 3) to hold information pertinent to referential integrity support.
If you need to add, drop, enable, or disable a referential integrity constraint that involves two tables, it is more convenient if you perform the operation before version-enabling the tables. However, you can add, drop, enable, or disable a referential integrity constraint that involves two version-enabled tables if you follow these steps:
Example 1-2 adds a foreign key constraint. Assume that the EMPLOYEE and DEPARTMENT tables are version-enabled and are defined as follows:
EMPLOYEE(emp_id number primary key, dept_id number) DEPARTMENT(dept_id number primary key, dept_name varchar2(30))
-- Begin a DDL session on the parent table. DBMS_WM.BeginDDL('DEPARTMENT'); -- Begin a DDL session on the child table. DBMS_WM.BeginDDL('EMPLOYEE'); -- Add the constraint between EMPLOYEE_LTS and DAPATMENT_LTS. ALTER TABLE employee_lts ADD CONSTRAINT employee_fk FOREIGN KEY (dept_id) REFERENCES department_lts(dept_id); -- Commit DDL on the child table (transfers the constraint on employee_lts -- to employee and drops employee_lts). EXECUTE DBMS_WM.CommitDDL('EMPLOYEE'); -- Commit DDL on the parent table (drops the department_lts table). EXECUTE DBMS_WM.CommitDDL('DEPARTMENT');
If you are in a DDL session (that is, if you have called the BeginDDL procedure), you cannot add, drop, enable, or disable a referential integrity constraint that involves two tables if one table is version-enabled and the other is not version-enabled. Both tables must be version-enabled.
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.
For any Workspace Manager procedure or function input parameter that calls for a table name, you can instead specify a synonym. When Workspace Manager looks for a table, it searches in the following sequence and uses the first match for the specified name:
The Workspace Manager application programming interface (API) consists of PL/SQL procedures in a single PL/SQL package. The procedures can be logically grouped into the 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 |
|---|---|
|
Version-enables a table, creating the necessary structures to enable the table to support multiple versions of rows. |
|
|
Deletes all support structures that were created to enable the table to support versioned rows. |
|
|
Disables the |
|
|
Enables the |
|
|
Starts a DDL (data definition language) session for a specified table. |
|
|
Commits DDL changes made during a DDL session for a specified table, and ends the DDL session. |
|
|
Rolls back (cancels) DDL changes made during a DDL session for a specified table, and ends the DDL session. |
|
|
Attempts to complete the migration process on a table that was left in an inconsistent state after the Workspace Manager migration procedure failed. |
|
|
Attempts to complete the migration process on all tables that were left in an inconsistent state after the Workspace Manager migration procedure failed. |
|
|
Allows LOB columns (BLOB, CLOB, or NCLOB) in version-enabled tables to be modified. |
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. |
|
|
Moves the current session 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 data changes made in the workspace to version-enabled tables. |
|
|
Discards all changes made in the workspace to a specified table (all rows or as specified in the |
|
|
Discards all data changes made in the workspace to version-enabled tables since the 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. |
|
|
Discards all row versions associated with a workspace and deletes the workspace. |
|
|
Discards all row versions associated with a workspace and its descendant workspaces, and deletes the affected workspaces. |
|
|
Restricts access to a workspace and the ability of users to make changes in the workspace. |
|
|
Enables access and changes to a workspace, reversing the effect of the FreezeWorkspace procedure. |
|
|
Deletes removable savepoints in a workspace, and minimizes the Workspace Manager metadata structures for the workspace. |
|
|
Deletes removable 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. |
|
|
Checks whether or not a workspace has any active sessions. |
|
|
Returns the current workspace for the session. |
|
|
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. |
|
|
Returns the context of the current operation for the current 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 whether or not conflicts exist between a workspace and its parent workspace. |
|
|
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 (makes permanent) any changes in the workspace since the BeginResolve procedure was executed. |
|
|
Quits a conflict resolution session and discards all changes in the workspace since the BeginResolve procedure was executed. |
Replication support procedures provide support for Oracle replication in a Workspace Manager environment. For information about using replication, see Appendix C.
Table 1-9 shows the procedures available for replication support.
| Procedure | Description |
|---|---|
|
Creates necessary structures for multimaster replication of Workspace Manager objects, and starts the master activity for the newly created master group. |
|
|
Deletes replication support objects that had been created by the GenerateReplicationSupport procedure. |
|
|
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.) |
|
|
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. |
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-3, 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 the market for one product (cola_b), 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) products. -- Each row will contain budget data for a specific -- product. 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 ( product_id NUMBER PRIMARY KEY, product_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 -- about data changes. 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: -- 1, cola_a, Alvarez, 2.0 -- 2, cola_b, Baker, 1.5 -- 3, cola_c, Chen, 1.5 -- 4, cola_d, Davis, 3.5 --------------------------------------------------------------------------- -- CREATE WORKSPACES -- --------------------------------------------------------------------------- -- Create workspaces for the following scenario: a major marketing focus -- for the cola_b product. Managers and budget amounts for each -- product 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 products' budgets). -- The other scenario (B_focus_2) features a manager with less expensive -- plans (which means less money taken from other products' 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 -- products' 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 product_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 3 WHERE product_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 1.5 WHERE product_name = 'cola_a'; UPDATE cola_marketing_budget SET budget = 1 WHERE product_name = 'cola_c'; UPDATE cola_marketing_budget SET budget = 3 WHERE product_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_1 workspace:: -- 1, cola_a, Alvarez, 1.5 -- 2, cola_b, Beasley, 3.0 -- 3, cola_c, Chen, 1.0 -- 4, cola_d, Davis, 3.0 -- Freeze this workspace to 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'); --------------------------------------------------------------------------- -- CREATE ANOTHER SCENARIO 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 product_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 2 WHERE product_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 3 WHERE product_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_2 workspace:: -- 1, cola_a, Alvarez, 2.0 (no change from LIVE) -- 2, cola_b, Burton, 2.0 -- 3, cola_c, Chen, 1.5 (no change from LIVE) -- 4, 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 product_name = 'cola_b'; UPDATE cola_marketing_budget SET budget = 2.5 WHERE product_name = 'cola_d'; COMMIT; -- Relevant data values now in B_focus_2 workspace: -- 1, cola_a, Alvarez, 2.0 (no change from LIVE) -- 2, cola_b, Burton, 2.5 -- 3, cola_c, Chen, 1.5 (no change from LIVE) -- 4, cola_d, Davis, 2.5 (same manager, new budget) -- Discard this scenario; roll back 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 a workspace 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 © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|