1.1 Workspace Manager Overview

Workspace Manager enables you to 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 Infrastructure for Version-Enabling of Tables; 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 Workspace Hierarchy.)

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

The history option enables you to 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.

• Workspace Hierarchy
  
• Using Savepoints
  
• Merging and Rolling Back Workspace Changes
  
• Resolving Conflicts Before a Merge or Refresh Operation
  
• Freezing and Unfreezing Workspaces
  
• Removing Workspaces
  
• Using Workspace Manager Events
  
• Autocommitting of Workspace Manager Operations
  
• Continually Refreshed Workspaces
  
• Multiparent Workspaces
  
• Infrastructure for Version-Enabling of Tables
  
• Creation of Row Versions and Historical Copies
  
• Workspace Manager Schema, Metadata, and Package
  

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, and the depth of the workspace hierarchy must not exceed 30 levels.) 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 "Figure 1-1 Workspace Tree"

See also Design Issue: Savepoint or Child Workspace? 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 "Figure 1-2 Savepoints"

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.

• Design Issue: Savepoint or Child Workspace?
  

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 enables you to 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.3 Merging and Rolling Back Workspace Changes

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.

• To roll back all changes made in the workspace, use the RollbackWorkspace procedure.

• To roll back changes made in the workspace after a savepoint, use the RollbackToSP procedure.

  Note:

  You cannot roll back to a savepoint if any implicit savepoints were 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 the figure in Using Savepoints, the user in Workspace1 cannot roll back to savepoint SP1 until Workspace3 (which caused implicit savepoint SPc to be created) is merged or removed.

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

1.1.4 Resolving Conflicts Before a Merge or Refresh Operation

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 (xxx_CONF) views. There is one conflict view for each table, as described in xxx_CONF Views. This view lists the column values of the rows in the two workspaces involved in the conflict.

You must resolve conflicts manually by setting the workspace conflict context for the session and then 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 base row is the currently visible row at the time of the first update or delete operation in the child workspace. In the case of an insert operation, the base row does not exist, except if the inserted row was previously deleted in an ancestor version of a parent workspace, the base row is that deleted row. The parent row is the currently visible row in the parent workspace at the time of the conflict resolution, and the child row is the currently visible row in the child workspace at the time of the conflict resolution.

Absence of data is not a conflict. In these cases, you can use the xxx_DIFF view (described in xxx_DIFF Views) to detect a change in one workspace when there is no row or no change to the row in the other workspace.

The general process for resolving conflicts is as follows:

1. Use the GotoWorkspace or SetConflictWorkspace procedure to set the workspace conflict context for the session.

2. Examine the xxx_CONF views (described in xxx_CONF Views) to see what conflicts exist.

3. Execute the BeginResolve procedure.

4. Execute the ResolveConflicts procedure as often as needed: once for each affected combination of table and workspace.

5. After resolving all conflicts, execute one of the following procedures:

   • CommitResolve if you want to make permanent all changes from the preceding step. (However, the changes are not made permanent in the database until you perform a standard database commit operation and execute the MergeWorkspace or RefreshWorkspace procedure, as described in the next step.)

   • RollbackResolve to discard all changes from the preceding step. (If you discard all changes, do not perform the next step.)

6. Perform a standard database commit operation and execute the MergeWorkspace or RefreshWorkspace procedure.

1.1.5 Freezing and Unfreezing Workspaces

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 DBMS_WM Package: Reference .)

Table 1-1 Freeze Results of Procedures

Procedure	Workspace and Mode
BeginResolve	Specified workspace: 1WRITER
MergeWorkspace	Specified workspace: NO_ACCESS Parent workspace: READ_ONLY
CompressWorkspace	Specified workspace: NO_ACCESS (Also, checks to ensure that there are no sessions on savepoints other than LATEST.)
CompressWorkspaceTree	Specified workspace: NO_ACCESS (Also, checks to ensure that there are no sessions on savepoints other than LATEST.)
CreateSavepoint	Specified workspace: READ_ONLY
DeleteSavepoint	Specified workspace: NO_ACCESS
CreateWorkspace	Specified workspace: READ_ONLY
RemoveWorkspace	Specified workspace: NO_ACCESS
RefreshWorkspace	Specified workspace: READ_ONLY Parent workspace: READ_ONLY
RollbackResolve	Specified workspace: 1WRITER
RollbackWorkspace	Specified workspace: NO_ACCESS

1.1.6 Removing Workspaces

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.

1.1.7 Using Workspace Manager Events

Several types of Workspace Manager operations can be captured as events, and can be communicated to applications through the Oracle Advanced Queuing (AQ) framework. Messaging features provided by AQ, such as asynchronous notification, persistence, propagation, access control, history, and rule-based subscription, can be used for Workspace Manager events.

Support for Workspace Manager events includes the ALLOW_CAPTURE_EVENTS Workspace Manager system parameter, the SetCaptureEvent procedure, and the WM_EVENTS_INFO metadata view.

Workspace Manager Events describes Workspace Manager events and explains how to use them in applications.

1.1.8 Autocommitting of Workspace Manager Operations

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 (DBMS_WM) subprograms 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 subprograms 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.

If the auto_commit parameter value is TRUE and any open transactions exist, the following considerations apply:

• For the CompressWorkspace and CompressWorkspaceTree procedures, an exception is raised if there is any open transaction.

• For all other Workspace Manager procedures, an exception is raised if there is an open transaction in a parent or child workspace of any table that needs to be modified.

1.1.9 Continually Refreshed Workspaces

A continually refreshed workspace is a workspace that is automatically refreshed from its parent workspace whenever data changes are committed in the parent workspace or are merged into the parent workspace from another child workspace. You do not need to call the RefreshWorkspace procedure for a continually refreshed workspace.

Any workspace in a branch of the workspace tree can be continually refreshed. A child workspace can be a continually refreshed workspace, regardless of whether its parent workspace is continually refreshed. However, if a parent workspace is a continually refreshed workspace, its child workspaces must also be continually refreshed.

To create a continually refreshed workspace, specify TRUE for the isrefreshed parameter in the call to the CreateWorkspace procedure. See the Usage Notes for the CreateWorkspace procedure for rules that apply to the creation of a continually refreshed workspace.

To change a workspace that is not continually refreshed to be continually refreshed, use the ChangeWorkspaceType procedure.

If a workspace is not continually refreshed, you must call the RefreshWorkspace procedure whenever you want to ensure that data changes in its parent workspace are visible in the 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 "Figure 1-3 Multiparent Workspace in a Workspace Hierarchy"

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 System Parameters for Workspace Manager.

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 DBMS_WM Package: Reference .

Workspace Manager provides the following static data dictionary views (described in Workspace Manager Static Data Dictionary Views ) 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.1.11 Infrastructure for Version-Enabling of Tables

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 static data dictionary views described in Workspace Manager Static Data Dictionary Views , 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. The added columns have names starting with WM_: WM_VERSION, WM_CREATETIME, WM_RETIRETIME, WM_NEXTVER, WM_DELSTATUS, and WM_LTLOCK.

Note that users and applications should not specify the <table-name>_LT table in SQL statements; they should continue to specify the original table name (<table-name>). (If you ever need to find the name of the <table_name>_LT table associated with a version-enabled table, or if you want to find out if a table is version-enabled by checking for the existence of a <table_name>_LT table, use the GetPhysicalTableName function.)

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.

Because Workspace Manager uses the original object name when it creates infrastructure objects, the effective maximum length of the name for some kinds of objects is shorter than the maximum permitted by Oracle AI Database. The database can be defined to support either 30 or 128 character identifiers based on the database's compatible setting. If the compatibility setting is >= 12.2 then the identifier length (<DB–identifier–length>) is 128; otherwise, it is 30.  The specific object length restrictions are expressed as an offset of (a value to be subtracted from) that <DB–identifier–length> value.

Table 1-2 provides guidelines for the maximum name length for version-enabled tables and related objects. (See also the information in Workspace Manager Reserved Words and Characters about reserved words and characters for certain names.)

Table 1-2 Name Length Guidelines for Workspace Manager

Type of Object	Maximum Name Length in Characters
Table	<DB–identifier–length> – 5
Column	<DB–identifier–length> – 2
Index	<DB–identifier–length> (<DB–identifier–length> — 4 if the index is created or altered between calls to the BeginDDL and CommitDDL procedures)
Trigger	<DB–identifier–length>
Constraint	<DB–identifier–length> (<DB–identifier–length> - 4 if the constraint is created or altered between calls to the BeginDDL and CommitDDL procedures)

Workspace Manager does not support SQL MERGE statement (any use), or the RETURNING clause with INSERT or UPDATE statements, on version-enabled tables. The RETURNING clause restriction is caused by the fact that Workspace Manager creates views with INSTEAD OF triggers on version-enabled tables, and Oracle AI Database does not support the RETURNING clause on views that have INSTEAD OF triggers defined on them.

1.1.12 Creation of Row Versions and Historical Copies

This section describes the process by which Workspace Manager creates new row versions and maintains historical copies of old versions.

A new row version is created in a version-enabled table when you do either of the following:

• Create an explicit savepoint in the current workspace, and update the row.

• Create a child workspace (using the CreateWorkspace procedure) under the current workspace, thus creating an implicit savepoint in the current workspace; execute the GotoWorkspace procedure to go to the child workspace; and update the row.

Any subsequent update of the current row overwrites the current row version, unless history is enabled on the table or another savepoint is created.

• If history is enabled without overwrite (VIEW_WO_OVERWRITE), each update of the current row version creates a full copy of the current row version with the changes and with a timestamp based on the transaction time. This copy becomes the new current row version.

• If history is enabled with overwrite (VIEW_W_OVERWRITE), each update of the current row version overwrites the current row version and updates the transaction timestamp.

• If a savepoint is created in the workspace, the next change to the row creates a new version, and the history cycle begins again.

Row versions created in a workspace are not visible from the parent workspace until you execute the MergeWorkspace or MergeTable procedure.

When you execute the MergeWorkspace procedure for a child workspace, only the current row version in the child workspace is merged into the parent workspace. If you specify the remove_workspace parameter as TRUE, any intermediate row versions in the child workspace are deleted when the child workspace is removed. To retain all intermediate versions created in the child workspace, the remove_workspace parameter value must be FALSE (the default).

When you execute the CompressWorkspace procedure on a child workspace to eliminate intermediate savepoints, you can also remove the associated historical copies of that row version. If you do not remove these copies, they are associated with the next version.

Intermediate row versions can only be selected for read-only access. To select an intermediate row version for read-only access, go to the workspace (GotoWorkspace procedure) if you are not already in it, and execute either the GotoDate procedure to set the session context to a historical time or the GotoSavepoint procedure to set the session context to a specific savepoint. Subsequent SELECT statements will select, for read-only access, the latest row version as of the specified date or savepoint.

Example 1-1 shows when row versions are created for a version-enabled table after the creation of an implicit savepoint and an explicit savepoint. For the example, assume that the EMP table contains columns named EMPNO, SALARY, and COMMISSION.

If Example 1-1 is executed for a table that has the VIEW_WO_OVERWRITE history option, then the xxx_HIST views (see xxx_HIST Views) will contain a single row for each DML operation. For example:

select salary, commission, wm_workspace, wm_optype from EMP_HIST where empno = 100;

SALARY	COMMISSION	WM_WORKSPACE	WM_OPTYPE
0	0	LIVE	I
10000	0	LIVE	U
10000	1000	LIVE	U
20000	1000	Workspace 1	U
20000	2000	Workspace 1	U
30000	3000	LIVE	U
40000	3000	LIVE	U
40000	4000	LIVE	U
40000	4000	LIVE	D

However, if Example 1-1 is executed for a table that has the VIEW_W_OVERWRITE or NONE history option, then the xxx_HIST views (see xxx_HIST Views) will contain only a single row for each savepoint, because the row version is overridden by subsequent DML operations within the same savepoint. For example:

select salary, commission, wm_workspace, wm_optype from EMP_HIST where empno = 100;

SALARY	COMMISSION	WM_WORKSPACE	WM_OPTYPE
10000	1000	LIVE	U
20000	2000	Workspace 1	U
30000	3000	LIVE	U
40000	4000	LIVE	D

Example 1-1 Row Versions Created After Implicit and Explicit Savepoints

execute dbms_wm.GotoWorkspace('LIVE') ;
insert into EMP(empno, salary, commission) values (100, 0, 0) ;
update EMP set salary = 10000 ;
update EMP set commission = 1000 ;
commit ;
 
–- CreateWorkspace creates an implicit savepoint in 'LIVE'; DML operations
–- create new row versions for both workspaces.
execute dbms_wm.CreateWorkspace('WorkSpace 1') ;
execute dbms_wm.GotoWorkspace('WorkSpace 1') ;
update EMP set salary = 20000 ;
update EMP set commission = 2000 ; 
commit ;
 
execute dbms_wm.GotoWorkspace('LIVE') ;
update EMP set salary = 30000, commission = 3000 ;
commit ;
 
-– CreateSavepoint creates an explicit savepoint in 'LIVE'; DML operations
-– create new row versions for only the LIVE workspace.
execute dbms_wm.CreateSavepoint('LIVE', 'SP1') ;
update EMP set salary = 40000 ;
update EMP set commission = 4000 ; 
delete EMP where empno = 100 ;
commit ;

1.1.13 Workspace Manager Schema, Metadata, and Package

Workspace Manager creates a user named WMSYS. The WMSYS schema is used to store all the metadata information for Workspace Manager.

Prior to Oracle AI Database 26ai, the secondary objects related to the workspace enabled tables (such as views, triggers, procedures, and so on) were created under the WMSYS schema. Therefore, the WMSYS schema had extensive privileges to access the data from the user schemas. However, from release 26ai onwards, all the secondary objects corresponding to the workspace enabled tables are stored under the user schema that owns these tables. WMSYS is only granted minimum privileges required to maintain the metadata stored under the WMSYS schema.

• See Secondary Metadata Objects Created in the User Schema for more information on the secondary objects that are created in the user schema.
• See Default WMSYS User Privileges for more information on WMSYS user privileges.
• See Migrating Metadata Post Upgrade to Release 26ai for more information on migrating metadata after upgrading to Oracle AI Database 26ai.

A PL/SQL package with the public synonym DBMS_WM contains the Workspace Manager subprograms (procedures and functions).

The following privileges are granted to the PUBLIC user group:

• SELECT privilege on Workspace Manager static data dictionary views (described in Workspace Manager Static Data Dictionary Views )

• EXECUTE privilege on the DBMS_WM package (described in DBMS_WM Package: Reference )

• Secondary Metadata Objects Created in the User Schema
  For each version-enabled table, in addition to the regular metadata objects, other secondary objects are created in the user schema.
• Default WMSYS User Privileges
  The default database privileges for the WMSYS schema are updated in Release 26ai.
• Migrating Metadata Post Upgrade to Release 26ai
  Starting from Oracle AI Database 26ai, all the secondary objects for workspace enabled tables are created in the user schema that owns these tables.

1.1.13.1 Secondary Metadata Objects Created in the User Schema

For each version-enabled table, in addition to the regular metadata objects, other secondary objects are created in the user schema.

These secondary objects are created with the prefix WM$. Note that prior to Release 26ai, these WM$ prefixed tables were created in the WMSYS schema.

For example, when you version enable a table as shown:

EXECUTE DBMS_WM.enableversioning('scott.dept_table');

Then the following similar secondary objects will be created in the user schema:

WM$PK_TMP_TAB_1$
WM$1$$
WM$1$_MW$
WM$1$_CONS$
WM$1$_HIST$
WM$1$_BPKC$
WM$1$_PKC$
WM$1$_CONF$
WM$1$_PKDB$
WM$1$_PKDC$
WM$1$_PKD$
WM$1$_DIFF$
WM$1$_LOCK$
DEPT_TABLE_CONF
DEPT_TABLE_DIFF
DEPT_TABLE_HIST
DEPT_TABLE_LOCK
DEPT_TABLE_MW
WM$1$_BASE$
DEPT_TABLE_LT
DEPT_TABLE
DEPT_TABLE_AUX

1.1.13.2 Default WMSYS User Privileges

The default database privileges for the WMSYS schema are updated in Release 26ai.

The following lists the default privileges granted to WMSYS user:

• ADMINISTER ROW LEVEL SECURITY POLICY
• INHERIT ANY PRIVILEGES
• SELECT ANY DICTIONARY
• ADMINISTER DATABASE TRIGGER
• EXECUTE ANY TYPE
• CREATE TABLE UNLIMITED TABLESPACE
• ALTER SESSION
• CREATE SESSION

1.1.13.3 Migrating Metadata Post Upgrade to Release 26ai

Starting from Oracle AI Database 26ai, all the secondary objects for workspace enabled tables are created in the user schema that owns these tables.

Prior to release 26ai, some of these secondary metadata objects were created under WMSYS schema. Due to this schema change, your earlier workspace enabled tables become unusable once you upgrade to release 26ai. Therefore, after your database upgrade is completed, connect to your database as a SYS user and perform the following post-upgrade steps:

GRANT INHERIT PRIVILEGES ON USER SYS TO WMSYS;
 
EXECUTE wmsys.owm_mig_pkg.enableversionTopoIndexTables;
EXECUTE wmsys.owm_mig_pkg.AllLwEnableVersioning(:old_owm_version_for_upgrade);
EXECUTE wmsys.owm_mig_pkg.recreatePtUpdDelTriggers;
EXECUTE wmsys.owm_mig_pkg.fixWMMetaData(:old_owm_version_for_upgrade);
EXECUTE wmsys.owm_mig_pkg.recompileAllObjects;
EXECUTE wmsys.owm_mig_pkg.modifySystemTriggers('ENABLE_T');
 
REVOKE INHERIT PRIVILEGES ON USER SYS FROM WMSYS;
 
SELECT * FROM wm_installation ORDER BY 1;
SELECT version, status FROM dba_registry WHERE comp_id='OWM';