8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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?
- WM_ADMIN_ROLE
- Version Enabling A Table
- Workspace Creation
- Export And Import Implications
- Versioning Restrictions
- Security
- Locks
- Advantages
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:
- Imports of version-enabled tables can only be performed if the target database has Workspace Manager installed and no workspaces defined other than LIVE.
- Only full database exports are supported with version-enabled databases.
- The IGNORE=Y parameter must be set for imports of version enabled databases.
- Imports of version-enabled databases cannot use the FROMUSER and TOUSER functioanlity.
Versioning Restrictions
Table Restrictions:
- The table must have a primary key.
- The table can only be version-enabled by the owner or a user with the
WM_ADMIN_ROLE
role. - Tables owned by SYS cannot be version-enabled.
Referential Integrity Constraint Restrictions:
- If the parent table is version-enabled the child table must be also.
- A child can be versioned when the parent is not.
- A versioned table cannot be both a parent and a child in a chain of constraints, except in self referencing constraints.
- Referential integrity constraints cannot be added after versioning is enabled. They must be present before version-enabling.
Trigger Restrictions:
- Triggers must be defined before version-enablinging.
- Only row-level triggers are supported.
- Only whole row triggers are supported. Not before and after triggers for specific columns.
- Triggers on nested tables are not supported.
- Only callouts to PL/SQL are supported.
- Any triggers that do not meet these restrictions are disabled when versioning is enabled and re-enabled when versioning is disabled.
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:
DBMS_WM.SetLocking[On/Off]
- Used to set the default locking for the session.DBMS_WM.SetWorkspaceLockMode[On/Off]
- When off, access to versioned rows and those in the corresponding parent workspace is allowed.DBMS_WM.GetLockMode
- Used to return the locking mode for the parent and child workspace.DBMS_WM.[Un]LockRows
- Used to lock rows in the parent schema during updates.
Advantages
- Allows data to be viewed as it was at a specific time.
- Allows changes to be rolled back to previous versions.
- Allows time-based analysis of data.
- Allows what-if analysis without affecting production data.
- Improved concurrency model.
- Automatic conflict detection.
- Conflict resolution using Oracle Enterprise Manager, PL/SQL and Java APIs.
- Reduces space wastage associated with multiple schemas.
- No alteration to application DML necessary.
- Easy management using Oracle Enterprise Manager.
- Full kernal support for constraints, locking, triggers and import/export.
For more information see:
Hope this helps. Regards Tim...