table structure behind oracle workspace manager

The VIEW_W_OVERWRITE parameter shows only the most recent change to a row. These are used to store the current version, next version, delete status and lock status respectively.Īn additional parameter HIST can be used to track changes to the table via a view called _HIST. The SCOTT.EMP_LT table has the following additional columns. This hides alot of the versioning mechanism from the users. The view uses instead-of triggers to perform all operations against the version enabled table. This will rename the SCOTT.EMP table to SCOTT.EMP_LT and create a view called SCOTT_EMP. A table can be version-enabled using the following command.ĮXEC DBMS_WM.EnableVersioning('SCOTT.EMP') The topmost workspace is always used to create a new workspace, irrespective of where it is in the workspace heirachy. Since only changed rows are stored, the storage overhead is minimized.

Version Enabling A TableĮvery 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. The DBA can choose to grant individual privileges or the WM_ADMIN_ROLE role to individual users. The WM_ADMIN_ROLE role has all Workspace Manager privileges and is granted to the DBA role by default. These conflicts can be corrected using Enterprise Manager or a programatic API. Once a workspace is refreshed, all changes can be cascaded down the heirachy.Ĭonflicts are detected automatically before changes are merged into the parent workspace. In addition it can see data from version-enabled tables in Live as they were when the PreProduction workspace was created. 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. 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. These changes are invisible to other workspaces until they are merged into a parent workspace. Changes to version-enabled tables are captured as new rows within the workspace. This allows several departments or functional areas to work against a single schema without interfering with data from other groups. Workspaces allows multiple transactionally consistent environments to exist within one database. In this article I will stick mainly with those aspects relevant to the Oracle9i Database: New Features For Administrators OCP exam. Workspace management is available for Oracle versions as old as 8.1.5, but in Oracle9i it is installed by default. Home » Articles » 9i » Here Workspace Management In Oracle9i

Table structure behind oracle workspace manager