1 Introduction to Workspace Manager

1.1 Workspace Manager Overview

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, although you cannot update a primary key column value in a version-enabled table. (Workspace Manager implements these capabilities by maintaining system views and creating INSTEAD OF triggers, as explained in Section 1.1.11; 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.

1.1.1 Workspace Hierarchy

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.

Figure 1-1 Workspace Tree

Description of the illustration vm_state_tree.gif

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

1.1.2 Using Savepoints

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 were created in the workspaces indicated. (Savepoints are indicated by dashed lines in Figure 1-2.)

Figure 1-2 Savepoints

Description of the illustration vm_savept.gif

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 DBMS_WM subprogram (procedure or function).

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:

• It is an explicit savepoint.

• It is an implicit savepoint that does not have any child dependencies.

1.1.2.1 Design Issue: Savepoint or Child Workspace?

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.

1.1.10 Multiparent Workspaces

A multiparent workspace is a child workspace that has two or more parent workspaces. A workspace is initially created with a single parent workspace. However, if the need arises, you can add other workspaces as parent workspaces to an existing workspace, thus making it a multiparent workspace. The multiparent workspace can see data from all of its parent workspaces and their ancestor workspaces, and it can be merged with and refreshed from its parent workspaces.

Figure 1-3 shows the same hierarchy of workspaces in Figure 1-1, except that Workspace3 is now a multiparent workspace with two parent workspaces: Workspace1 and Workspace4.

Figure 1-3 Multiparent Workspace in a Workspace Hierarchy

Description of the illustration multiparent_ws.gif

A multiparent workspace is also called a multiparent leaf workspace. Thus, in Figure 1-3, Workspace3 is a multiparent leaf workspace. The nearest common ancestor of all parent workspaces of a multiparent lead workspace is called the multiparent root workspace. In Figure 1-3, the LIVE workspace is the multiparent root workspace of Workspace3. All of the workspaces in the directed acyclic graph (DAG) formed as a result of adding parent workspaces as parents of a leaf workspace are called multiparent graph workspaces. In Figure 1-3, Workspace1, Workspace4, and Workspace3 are the multiparent graph workspaces.

Multiparent workspaces are allowed only if the ALLOW_MULTI_PARENT_WORKSPACE Workspace Manager system parameter is set to ON. In addition, for a continually refreshed workspace to be a multiparent workspace, the CR_WORKSPACE_MODE Workspace Manager system parameter must be set to PESSIMISTIC_LOCKING; and for a workspace that is not continually refreshed to be a multiparent workspace, the NONCR_WORKSPACE_MODE Workspace Manager system parameter must be set to PESSIMISTIC_LOCKING. For information about Workspace Manager system parameters, see Section 1.5.

To create a multiparent workspace, use the AddAsParentWorkspace procedure. To remove a workspace as a parent of a multiparent workspace, use the RemoveAsParentWorkspace procedure. To grant and revoke privileges on multiparent graph workspaces, use the GrantGraphPriv and RevokeGraphPriv procedures, respectively. These procedures are described in Chapter 4.

Workspace Manager provides the following metadata views (described in Chapter 5) to store information about multiparent workspaces:

• USER_MP_GRAPH_WORKSPACES and ALL_MP_GRAPH_WORKSPACES contain information about multiparent graph workspaces.

• USER_MP_PARENT_WORKSPACES and ALL_MP_PARENT_WORKSPACES contain information about parent workspaces of multiparent leaf workspaces.

1.2 Session Context Information for Workspace Manager

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

• A savepoint name: The session is currently set to a savepoint in the workspace. The session cannot see changes as they are made in the latest version of the workspace, but instead sees a static view of the data as of the savepoint creation time. The session context is set to the savepoint name after a call to the GotoDate procedure.

• An instant (a point in time): The session is currently set to a specific point in time. The session cannot see changes as they are made in the latest version of the workspace, but instead sees a static view of the data as of the specific time. The session context is set to an instant after a call to the GotoDate procedure. (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.)

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 performed a combination of GotoWorkspace, GotoDate, and GotoDate operations.

1.3 Lock Management with Workspace Manager

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:

• Lock at the workspace level (SetWorkspaceLockModeON procedure) if the data changes are in one or a few workspaces, or if you want all data changes in the workspace to be locked.

• Lock at the session level (SetLockingON procedure) if the data changes are being made in many workspaces. When locking is enabled for a session, Workspace Manager locks rows in all workspaces in which the session participates.

• Lock specific rows (LockRows procedure) either to lock the rows before they are updated or to automatically lock rows after they are inserted (or updated if they satisfy the 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:

• Exclusive locks - The locks are very similar to database transaction locks in that once an exclusive lock is placed on a record, no other user in the database can change the record except for the session (user) that locked it. When exclusive locking is enabled for a user, any row that the user changes is locked exclusively. In addition, the parent row to that row is also locked exclusively. Thus, exclusive locking can be used to eliminate data conflicts between a child and its parent workspace.

• Shared locks - Once a shared lock is placed on a row, only users in the workspace in which it is locked are allowed to modify it. Shared locks are also placed on the parent version of the row, thus protecting the row from conflicts. The benefit of shared locks over exclusive locks is that all users in the workspace where the row is locked can access the row for changes. An ideal use for this kind of lock is on a row that needs to have no conflicts with its parent, but that needs to be changed by a collection of users participating in a group project. Note that shared locking must be individually enabled for each session in the workspace.

Workspace-exclusive locks and version-exclusive locks are forms of exclusive locking that control which users can and cannot change data values, but (unlike exclusive locking) they do not prevent conflicts from occurring. Workspace-exclusive locks lock rows such that only the user that set the lock can change the values in the current workspace; however, other users in other workspaces can change the values. Version-exclusive locks lock rows such that only the user that set the lock can change the values (and that user can be in any workspace); no other users (in any workspace) can change the values.

Table 1-2 indicates, for a row locked by a specific user in a specific workspace, which users in which workspaces can and cannot modify the row. For example, the first two entries in Table 1-2 mean that when a shared (S) lock is placed on a row, any user in the workspace in which the row was locked can modify the row, but any user in a workspace different from the workspace in which the row was locked cannot modify the row.

The xxx_LOCK metadata views (described in Section 5.44) contain information about locks in each version-enabled table.

1.4 Privilege Management with Workspace Manager

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-3 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.

1.5 System Parameters for Workspace Manager

Workspace Manager provides a set of system parameters that allow a user with the WM_ADMIN_ROLE role (described in Section 1.4) to enforce global Workspace Manager-specific settings for the database. (These Workspace Manager system parameters are not Oracle initialization parameters. The only way to set Workspace Manager system parameters is to use the SetSystemParameter procedure, described in Chapter 4).

To set a system parameter, use the SetSystemParameter procedure. To get the current setting for a system parameter, use the GetSystemParameter procedure. Both procedures are described in Chapter 4.

Table 1-4 lists the Workspace Manager system parameters.

1.6 Import and Export Considerations

Standard Oracle database import and export operations can be performed on version-enabled databases; however, the following considerations and restrictions apply:

• A database with version-enabled tables can be exported to another Oracle database only if the other database has Workspace Manager installed and does not currently have any version-enabled tables or workspaces (that is, other than the LIVE workspace).

• Only database-wide import and export operations are supported for version-enabled databases. No other export modes (such as schema, table, partition, and workspace) are supported.

• For an import operation, you must specify IGNORE=Y.

• The FROMUSER and TOUSER capabilities of the Oracle9i Import utility are not supported with version-enabled databases.

1.7 Bulk Loading into Version-Enabled Tables

You can use SQL*Loader to perform bulk loading into version-enabled tables, but you must also call some special Workspace Manager procedures, and some restrictions apply. You can perform both direct-path and conventional-path bulk loading of data into either the latest version of any workspace or into the root version (version number 0, which is in the LIVE workspace). The root version is the ancestor of all other versions, so data in the root version is visible from all other workspaces (unless non-LIVE workspaces have updated the data).

Follow these general steps for bulk loading into a version-enabled table:

1. Call the GetBulkLoadVersion function to fetch the reserved version number with which the bulk loaded data needs to be tagged. All data bulk loaded into the versioned table needs to be tagged with a version number that depends upon the final destination of the data, namely, the latest version of a workspace or the root version. Use the version number returned by the GetBulkLoadVersion function in the SQL*Loader control file, as explained in step 3.

2. Call the BeginBulkLoading procedure to prepare the table for bulk loading. When data is being bulk loaded into a version-enabled table, DML and workspace operations on the table are not allowed, although workspace operations that do not involve this table are allowed. The BeginBulkLoading procedure prevents invalid operations from being performed on this table.

3. Use SQL*Loader to perform the bulk loading. Only one line needs to be changed in the control file, to specify the <table_name>_LT name and to include the version number fetched in step 1. For example, assume that the existing control file has the following line:

   Load data  into table departments (name, loc)
   
   

   If the version number fetched in step 1 is 5, the line in the control file for bulk loading into the version-enabled table should be changed to:

   Load data into table departments_LT (name, loc, version constant '5')
   
   

   This ensures that all the bulk-loaded rows will be tagged with version 5, and that the other Workspace Manager-specific columns for these rows will have null values. If the table was version-enabled with the history option, create and retire times can be bulk loaded into the createtime and retiretime columns of <table_name>_LT.

4. Complete the bulk loading process by calling either the CommitBulkLoading procedure to commit the bulk loading changes or the RollbackBulkLoading procedure to roll back the bulk loading changes.

If you commit the bulk loading changes, Workspace Manager ensures that the data is updated in the required workspace and version. By default, the bulk-loaded data is checked for each unique or referential constraint defined on the table, and any bulk-loaded rows that are in violation of any constraints are moved to a discards table specified as a parameter to the CommitBulkLoading procedure. If you specified to check for duplicates (that is, records in the data to be bulk loaded that have the same values in the primary key columns), for any duplicate records only the record with the lowest ROWID value is loaded into the table, and the rest are moved to the discards table.

The following restrictions apply to bulk loading with version-enabled tables in the current release:

• Bulk loading into a table with a self-referential integrity constraint is not allowed.

• Bulk loading into a workspace, other than LIVE, that has continually refreshed child workspaces is not allowed.

• Only the owner of a table or a user with the WM_ADMIN_ROLE role can bulk load into a version-enabled table.

• The user that is bulk loading the version-enabled table must have the INSERT privilege for <table_name>_LT.

• User-defined triggers on version-enabled tables are not executed during bulk loading.

• Session locking mode is not enforced for the bulk-loaded rows. Use the LockRows procedure to lock these rows.

1.8 DDL Operations Related to Version-Enabled Tables

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:

• Column-related: 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; changing the length of a column of type VARCHAR2, VARCHAR, CHAR, NCHAR, NVCHAR, or NVCHAR2; changing the scale or precision of a column of type NUMBER)

  Note that any new length, scale, or precision for a column should be adequate for any existing data in the column.

• Index-related: CREATE INDEX, DROP INDEX, ALTER INDEX (but for ALTER INDEX only the following options: logging, pctfree, initrans, initialextent, minextents, nextextent, maxextents, pctincrease, freelists, freelist groups, and buffer_pool)

  If the name of the index on a version-enabled table is longer than 26 characters, you must use the AlterVersionedTable procedure if you want to rename the index; you cannot use the ALTER INDEX statement with the RENAME clause. If the name of the index on a version-enabled table is 26 or fewer characters long, you can do either of the following to rename the index: use the AlterVersionedTable procedure, or use the ALTER INDEX statement with the RENAME clause between calls to the BeginDDL and CommitDDL procedures. See the Usage Notes for AlterVersionedTable for more information.

• Trigger-related: CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER ENABLE/DISABLE

• Referential integrity constraint-related: add, drop, enable, or disable a referential integrity constraint. For information about Workspace Manager referential integrity support, see Section 1.9.1.

• Unique constraint-related: add, drop, enable, or disable a unique constraint. For information about Workspace Manager unique constraint support, see Section 1.9.2.

If 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 a domain index (for example, creating an R-tree 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');

1.9 Constraint Support with Workspace Manager

This section describes Workspace Manager considerations relating to the use of database constraints.

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'); 

1.10 Triggers on Version-Enabled Tables

Version-enabled tables can have triggers defined; however, the following considerations and restrictions apply:

• Only per-row triggers are supported. Per-statement triggers are not supported.

• Only whole-row triggers are supported. Before-update and after-update triggers for specific columns are not supported.

• The only call-out supported is to PL/SQL procedures. That is, the 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.

You can selectively enable specific user-defined triggers for certain kinds of events by using the SetTriggerEvents procedure.

1.11 Virtual Private Database Considerations

You can use Workspace Manager in conjunction with the Oracle Virtual Private Database (VPD) technology. (Virtual private databases are described in Oracle Security Overview.) However, the following considerations apply Workspace Manager in a VPD:

• Row-level security policies are not enforced during workspace operations, such as MergeWorkspace. A call to MergeWorkspace will merge all the changes made in a workspace, not just the changes that the current user can see. You can use Workspace Manager privileges (such as MERGE_WORKSPACE) to control workspace operations.

• Row-level security policies cannot be defined on a version-enabled table by defining them only on the specified table (<table_name>). Instead, you must define row-level security policies on all of the following that exist: <table_name>, <table_name>_LOCK, <table_name>_CONF, <table_name>_DIFF, and <table_name>_HIST. Do not use the Workspace Manager DDL framework described in Section 1.8 (that is, do not use the BeginDDL and CommitDDL procedures) when defining row-level security policies.

1.12 Support for Table Synonyms

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:

1. A table in the specified schema (or local schema if no schema is specified)

2. A private synonym in the specified schema (or local schema if no schema is specified)

3. A public synonym

1.13 Materialized View Support

This section describes considerations for using Workspace Manager with materialized views.

You can create a materialized view on a version-enabled table only if you specify the complete refresh method (REFRESH COMPLETE) when you create the materialized view. You cannot specify any of the following clauses in the CREATE MATERIALIZED VIEW statement:

• FAST (incremental refresh)

• ON COMMIT

• FOR UPDATE

You cannot version-enable a materialized view or the base table of a materialized view.

When the materialized view is created, its content is based on the workspace in which the session is at that time. When the materialized view is refreshed, its content is based on the workspace in which the session is when the DBMS_MVIEW.REFRESH operation is performed. When the materialized view is created or refreshed, it shows the same data in all workspaces.

1.14 Spatial Topology Support

This section describes special considerations and techniques for using Workspace Manager with tables in Oracle Spatial topologies, which are documented in Oracle Spatial Topology and Network Data Models.

A topology consists of feature tables, as well as tables with names in the form <topology-name>_NODE$, <topology-name>_EDGE$, <topology-name>_FACE$, <topology-name>_RELATION$, and <topology-name>_HISTORY$. If you want to version-enable any topology tables, you must version-enable all tables associated with the topology. To do so, you must specify the topology name as the table_name parameter to the EnableVersioning procedure, and you must specify the isTopology parameter as TRUE. For example:

EXECUTE DBMS_WM.EnableVersioning(table_name => 'xyz_topo', isTopology => TRUE);

The preceding example version-enables the xyz_topo topology; that is, it version-enables all feature tables associated with the xyz_topo topology, as well as the XYZ_TOPO_NODE$, XYZ_TOPO_FACE$, XYZ_TOPO_EDGE$, XYZ_TOPO_RELATION$, and XYZ_TOPO_HISTORY$ tables.

A version-enabled topology must have at least one feature table.

To disable versioning on any topology tables, you must disable versioning on all tables associated with the topology by specifying the topology name as the table_name parameter to the DisableVersioning procedure and the isTopology parameter as TRUE.

However, exceptions apply to the preceding guidelines about version-enabling and version-disabling topology tables in the following cases:

• If a feature table of a topology is the child table of a referential integrity constraint with CASCADE option with a table that is not in the topology

• If a feature table of a topology is the parent table of a referential integrity constraint with a table that is not in the topology

In these cases, you must version-enable or version-disable the feature table separately. That is, first call the EnableVersioning or DisableVersioning procedure on the feature table (along with any tables required by the referential integrity constraint), and then invoke the EnableVersioning or DisableVersioning procedure specifying the topology name.

EXECUTE DBMS_WM.LockRows (workspace => 'ws1', table_name => 'xyz_topo', Xmin => 0.1, Ymin => 0.1,  Xmax => 0.5, Ymax => 0.5 );

1.15 DBMS_WM Subprogram Categories

The Workspace Manager application programming interface (API) consists of PL/SQL subprograms (procedures and functions) in a single PL/SQL package named DBMS_WM. The subprograms can be logically grouped into the categories described in this section.

Reference information for all subprograms is in Chapter 4.

1.16 Simplified Examples Using Workspace Manager

This section presents two simplified examples of using Workspace Manager to try out some scenarios and select one of them. Each example uses workspaces and one or more savepoints. One example (in Section 1.16.2) uses the OE.WAREHOUSES table in the Oracle sample schemas.

The examples refer to concepts that were explained in this chapter, and they use procedures documented in Chapter 4.

-------------------------------------------------------------------
-- 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).
EXECUTE DBMS_WM.GotoWorkspace ('B_focus_2');
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 not 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);

-------------------------------------------------------------------
-- INITIAL SET-UP
-------------------------------------------------------------------
-- Clean up from any previous running of this procedure.
DROP USER wm_developer CASCADE;

-- 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 privileges on tables to be modified.
GRANT select, insert, delete, update ON oe.warehouses TO wm_developer; 
GRANT select, insert, delete, update ON hr.locations 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');

-- WM_ADMIN_ROLE required to version-enable a table in another schema.
GRANT wm_admin_role TO wm_developer;

-- Create rows for new locations, since a valid location ID is needed for each
-- proposed new warehouse.
INSERT INTO hr.locations VALUES 
   (4000, '123 Any Street', '01234', 'Town A', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4100, '456 Some Street', '01235', 'Town B', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4200, '789 Other Street', '01236', 'Town C', 'MA', 'US');
INSERT INTO hr.locations VALUES 
   (4300, '1 Yetanother Street', '01237', 'Town D', 'MA', 'US');

---------------------------------------------------------------------------
-- CREATE AND VERSION-ENABLE THE DATA TABLE --
---------------------------------------------------------------------------
CONNECT wm_developer/wm_developer
set echo on
set serveroutput on

-- Version-enable the OE.WAREHOUSES table. Specify hist option of 
-- VIEW_WO_OVERWRITE so that the WAREHOUSES_HIST view contains 
-- complete history information about data changes. However, because 
-- OE.WAREHOUSES is the parent table in a referential integrity constraint
-- with OE.INVENTORIES, you must also version-enable that table.

EXECUTE DBMS_WM.EnableVersioning ('OE.WAREHOUSES, OE.INVENTORIES', hist => 'VIEW_WO_OVERWRITE');

------------------------------------------------------------------------
-- CREATE AND USE WORKSPACES --
------------------------------------------------------------------------
-- The company has decided that it needs additional warehouse space. 
-- It wants to consider two scenarios: a single large warehouse in Town A,
-- and two smaller warehouses in Town B and Town C that together offer more
-- total storage capacity. There are potential advantages and disadvantages 
-- to each scenario, and financial and legal issues to be resolved with each.
--
-- Later, the company decides that it might need even more warehouse
-- space under each scenario, so it wants to consider the same additional 
-- warehouse in each scenario.

-- Create a workspace for each scenario, with both created as child 
-- workspaces of the LIVE database workspace.
-- In workspace large_warehouse, add one row for the single large warehouse.
-- In workspace smaller_warehouses, add two rows, one for each warehouse.
--
-- Also, within each workspace create a savepoint before adding the
-- extra warehouse, because the company might decide it does not
-- need the warehouse.

EXECUTE DBMS_WM.CreateWorkspace (workspace => 'large_warehouse');
EXECUTE DBMS_WM.CreateWorkspace (workspace => 'smaller_warehouses');

-- Set up the first scenario: Go to the large_warehouse workspace and first add
-- one row for a warehouse.

EXECUTE DBMS_WM.GotoWorkspace (workspace => 'large_warehouse');

INSERT INTO oe.warehouses VALUES (10, NULL, 'Town A', 4000,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00703, 42.27099, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Owned</Building> 
<Area>100000</Area> 
<Docks>2</Docks> 
<DockType>Side load</DockType> 
<WaterAccess>Y</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Lot</Parking> 
<VClearance>15 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 10; 

COMMIT;

-- Create a savepoint so that you can, if necessary, roll back to the point
-- before the extra warehouse was added.
EXECUTE DBMS_WM.CreateSavepoint ('large_warehouse', 'large_warehouse_add_wh');

-- Add another warehouse for this scenario.
INSERT INTO oe.warehouses VALUES (11, NULL, 'Town D', 4300,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00707, 42.35226, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>55000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>N</RailAccess> 
<Parking>Street</Parking> 
<VClearance>10 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 11; 

COMMIT;

-- Freeze this workspace to prevent any changes until the 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 ('large_warehouse');

-- Set up the second scenario: Go to the smaller_warehouses workspace and first 
-- add two rows for the smaller warehouses.

EXECUTE DBMS_WM.GotoWorkspace ('smaller_warehouses');

INSERT INTO oe.warehouses VALUES (10, NULL, 'Town B', 4100,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.02439, 42.28628, NULL), NULL, NULL)); 

INSERT INTO oe.warehouses VALUES (11, NULL, 'Town C', 4200,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-70.97980, 42.37961, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Owned</Building> 
<Area>60000</Area> 
<Docks>1</Docks> 
<DockType>Side load</DockType> 
<WaterAccess>Y</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Lot</Parking> 
<VClearance>15 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 10; 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>550000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>Y</RailAccess> 
<Parking>Street</Parking> 
<VClearance>12 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 11; 

COMMIT;

-- Create a savepoint so that you can, if necessary, roll back to the point
-- before the extra warehouse was added.
EXECUTE DBMS_WM.CreateSavepoint ('smaller_warehouses', 'smaller_warehouses_add_wh');

-- Add the extra warehouse for this scenario.
INSERT INTO oe.warehouses VALUES (12, NULL, 'Town D', 4300,
        MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(-71.00707, 42.35226, NULL), NULL, NULL)); 

UPDATE oe.warehouses SET warehouse_spec = sys.xmltype.createxml( 
'<?xml version="1.0"?> 
<Warehouse> 
<Building>Leased</Building> 
<Area>55000</Area> 
<Docks>1</Docks> 
<DockType>Rear load</DockType> 
<WaterAccess>N</WaterAccess> 
<RailAccess>N</RailAccess> 
<Parking>Street</Parking> 
<VClearance>10 ft</VClearance> 
</Warehouse>' 
) WHERE warehouse_id = 12; 

COMMIT;

---------------------------------------------------------------------------
-- SELECT A SCENARIO, AND APPLY IT --
---------------------------------------------------------------------------
-- Later, the company makes its decisions:
-- 1. Add two smaller warehouses.
-- 2. Do not add the extra warehouse (that is, no third new warehouse).
-- Consequently, you need to discard the first scenario (large_warehouse
-- workspace) completely, discard the warehouse addition in the second
-- scenario (roll back to smaller_warehouses_add_wh savepoint), and 
-- apply the second scenario.

-- 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 ('large_warehouse');
EXECUTE DBMS_WM.RemoveWorkspace ('large_warehouse');

-- Rollback the workspace for the second scenario to the savepoint created
-- before the extra warehouse was added.
EXECUTE DBMS_WM.RollbackToSP ('smaller_warehouses', 'smaller_warehouses_add_wh');

-- Apply changes in the smaller_warehouses workspace to the LIVE database 
-- workspace; use the remove_workspace parameter to remove the 
-- smaller_warehouses workspace after the merge.
EXECUTE DBMS_WM.MergeWorkspace ('smaller_warehouses', remove_workspace => TRUE);

-- The OE.WAREHOUSES table now has the desired data (two additional warehouses
-- from the smaller_warehouses scenario). Display the IDs and names just to be
-- sure.
SELECT warehouse_id, warehouse_name FROM oe.warehouses 
   ORDER BY warehouse_id;

-- Disable versioning on the table because you are finished testing scenarios.
-- Set the force parameter to TRUE to force disabling even though changes 
-- were made in a non-LIVE workspace. You must also version-disable
-- the other tables previously version-enabled (along with OE.WAREHOUSES).

EXECUTE DBMS_WM.DisableVersioning ('OE.WAREHOUSES, OE.INVENTORIES', force => TRUE);

-- Clean up by deleting the rows that were added to the OE.WAREHOUSES table.
DELETE FROM oe.warehouses WHERE warehouse_id >= 10;

-- Clean up by deleting the locations that were added.
DELETE FROM hr.locations WHERE location_id >= 4000;

SELECT warehouse_id, warehouse_name FROM oe.warehouses 
   ORDER BY warehouse_id;

WAREHOUSE_ID WAREHOUSE_NAME                                                     
------------ -----------------------------------                                
           1 Southlake, Texas                                                   
           2 San Francisco                                                      
           3 New Jersey                                                         
           4 Seattle, Washington                                                
           5 Toronto                                                            
           6 Sydney                                                             
           7 Mexico City                                                        
           8 Beijing                                                            
           9 Bombay                                                             
          10 Town B                                                             
          11 Town C