Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Workspace Management In Oracle9i

Workspace management is available for Oracle versions as old as 8.1.5, but in Oracle9i it is installed by default. In this article I will stick mainly with those aspects relevant to the Oracle9i Database: New Features For Administrators OCP exam.

What Are Workspaces For?

Workspaces allows multiple transactionally consistent environments to exist within one database. This allows several departments or functional areas to work against a single schema without interfering with data from other groups. Changes to version-enabled tables are captured as new rows within the workspace. These changes are invisible to other workspaces until they are merged into a parent workspace.

Multiple versions of a row can exist within a workspace, with the active or current row being the one to which changes are currently being made.

In a workspace heirachy consisting of Live->PreProduction->Development workspaces, the Development workspace can see all row changes made in the PreProduction workspace, along with all commited data from non-version-enabled tables belonging to the Live workspace. In addition it can see data from version-enabled tables in Live as they were when the PreProduction workspace was created. Once a workspace is refreshed, all changes can be cascaded down the heirachy.

Conflicts are detected automatically before changes are merged into the parent workspace. These conflicts can be corrected using Enterprise Manager or a programatic API.

WM_ADMIN_ROLE

The WM_ADMIN_ROLE role has all Workspace Manager privileges and is granted to the DBA role by default. The DBA can choose to grant individual privileges or the WM_ADMIN_ROLE role to individual users.

Version Enabling A Table

Every time a change is made to a version-enabled table a new row is created to reflect the change, with all new rows being stored in the same tablespace. Since only changed rows are stored, the storage overhead is minimized. The topmost workspace is always used to create a new workspace, irrespective of where it is in the workspace heirachy. A table can be version-enabled using the following command.

EXEC DBMS_WM.EnableVersioning('SCOTT.EMP');

This will rename the SCOTT.EMP table to SCOTT.EMP_LT and create a view called SCOTT_EMP. The view uses instead-of triggers to perform all operations against the version enabled table. This hides alot of the versioning mechanism from the users.

The SCOTT.EMP_LT table has the following additional columns.

VERSION    NOT NULL NUMBER(38)
NEXTVER             VARCHAR2(500)
DELSTATUS           NUMBER(38)
LTLOCK              VARCHAR2(100)

These are used to store the current version, next version, delete status and lock status respectively.

An additional parameter HIST can be used to track changes to the table via a view called <table_name>_HIST. The default is no history. The VIEW_W_OVERWRITE parameter shows only the most recent change to a row. The VIEW_NO_OVERWRITE parameter shows all modifications to a row.

Table-versioning can be disabled using the following command.

EXEC DBMS_WM.DisableVersioning('SCOTT.EMP');

An additional boolean parameter FORCE can be set to disable versioning even if workspaces contain modified data.

Workspace Creation

A workspace can be created using the following command.

EXEC DBMS_WM.CreateWorkspace('MYWORKSPACE1');

The resulting workspace is a child of the current workspace. Creation of a workspace creates an implicit savepoint in the parent workspace. After creation you are not automatically placed in the new workspace. This switch must be done explicitly.

EXEC DBMS_WM.GotoWorkspace('MYWORKSPACE1');

All logins place the user in the LIVE workspace. The user must then manually switch to their preferred workspace.

A workspace that is no longer needed can be removed as follows.

DBMS_WM.RemoveWorkspace('MYWORKSPACE1');

Export And Import Implications

The following implications are a result of version-enabling tables:

Versioning Restrictions

Table Restrictions:

Referential Integrity Constraint Restrictions:

Trigger Restrictions:

Security

The ACCESS, CREATE, REMOVE, MERGE and ROLLBACK workspace privileges can be granted to a user for a specific workspace or any workspace as follows.

EXEC DBMS_WM.GrantWorkspacePriv('ACCESS_WORKSPACE, MERGE_ANY_WORKSPACE', 'MYWORKSPACE1','SCOTT','NO');

The last parameter indicates whether the privilege is granted with the grant option.

Locks

In addition to regular locks, workspace manager specific locks are used to eliminate conflicts between workspaces. Locking is session specific and independant of the current workspace, allowing locking of appropriate records irrespective of their workspace. Exclusive locks ('E') are used to prevent mofication of rows by any other session, while shared locks ('S') allow rows to be modified by any session in the issuing workspace.

Typical locking operations are:

Advantages

For more information see:

Hope this helps. Regards Tim...

Back to the Top.