Oracle Workspace Manager + APEX + VPD
This is an article I wrote quite some time ago and put “on ice” until I completed the client project it was related to. However, for unrelated reasons the project was significantly delayed and later rebooted with an updated set of requirements, which no longer require Oracle Workspace Manager. This means I’m unable to add a triumphant postscript saying “this was a great success and it’s being used to this day”. Instead, I’m adding this preamble to say “this was a very interesting feature of Oracle I learned but didn’t quite get to use, but hopefully someone will find it useful”.
Oracle Workspace Manager (OWM) is a feature of the Oracle database that was built back in the 8i days and installed in Oracle by default since 9i. As described by Tim Hall, it allows multiple transactionally consistent environments to exist within one database.
Don’t confuse OWM with Oracle APEX workspaces, or with Analytic Workspace Manager for Oracle OLAP.
OWM allows the developer to take a leap over the complexities involved in a number of use cases, such as:
- Savepoints – a snapshot of a point in time
- Workspaces – a private area for modifications that are not visible to normal users
- Row History – store a history of every change for every record
- Valid Time – support date/time range validity for each record
The Row History use case is similar to using Flashback Query which is a more modern feature of the database; however, since it can be enabled or disabled individually for each table, it may require less storage space to support querying back as far as the user would like in time.
The Valid Time use case allows your users to set a date/time range on each record; multiple versions of a unique row can be created with non-overlapping date ranges; updates can be done within the context of a given date/time range, which will cause rows that span the boundary of the range to be split into multiple versions. I haven’t tried this myself but it looks like a powerful feature.
Note: the default workspace for a user session is called “
LIVE“, and the default savepoint is “
One example of using savepoints is that a user could create a savepoint, make changes, go back and view the database as of the savepoint, and can rollback all changes to a savepoint. They can also run an API command (
dbms_wm.SetDiffVersions) to generate a differences report which shows all the inserts, updates, and deletes that have occurred since a savepoint.
An example of using workspaces is where a user could create one or two workspaces, each representing a different scenario. The user can jump back and forth between the scenarios and the “live” workspace (which is the default). They can edit any of these workspaces. Changes made by other users to the “live” workspace may, optionally, be automatically propagated into a workspace. Similarly to savepoints, the user can get a differences report between any workspace (including the “live” workspace). In addition, the user can create and view savepoints and additional workspaces within a workspace – OWM maintains a hierarchy of workspaces.
If the user is not happy with their scenario, they can simply delete the workspace and the live data is not affected.
If they are happy with their changes in a workspace, they can choose to do a Merge – which attempts to effect all the inserts, updates and deletes that were made in the workspace to its parent workspace (e.g. the “live” workspace if that was the source). If any change to a row would conflict with a change that another user made to the same row in the parent workspace, the merge stops and the user may be prompted to resolve the conflicts (i.e. for each row, the user can decide to refresh their workspace with the updated data in the parent, or they can force their workspace’s change onto the parent).
I suspect OWM was once known (or internally referred to) as “Long Transactions” or something like that, probably because of its user workspace and merging features. You can see the remnants of this old name in the documentation – many of the document URLs start with “long”. Also, note the title of this slide deck by an Oracle product manager: “Long Transactions with Oracle Database Workspace Manager Feature”.
The features of OWM only affect the tables that you explicitly Enable Versioning on via the API (DBMS_WM).
Limitations/Restrictions of Workspace Manager
If you are looking into using OWM, you must read the Intro to Workspace Manager in the docs. Make sure to review the restrictions that apply, such as:
- Each version-enabled table must have a primary key.
- If a parent table is version-enabled, each child table must also be version-enabled (but the opposite does not apply).
- Referential integrity constraints MUST refer to the primary key in the parent table.
- Primary key values in a parent table cannot be updated.
- Only row-level triggers are supported – no per-statement triggers.
- SQL MERGE statements are not allowed (attempts to do so results in “ORA-01400 cannot insert NULL into WM_VERSION”).
- RETURNING clause is not supported (for INSERT or UPDATE statements).
- Row-level security policies (VPD) are not enforced during workspace operations (such as Merge Workspace, and Rollback to Savepoint).
- Materialized Views on a version-enabled table can only use the
REFRESH COMPLETEmethod; e.g. no
- Table names cannot be longer than 25 characters.
- Column names cannot be longer than 28 characters.
- Trigger names cannot be longer than 27 characters.
- Some reserved words and characters apply, e.g. column names cannot start with
- Most DDL operations cannot be run directly on a version-enabled table (see below).
There are some other restrictions, so make sure to review the intro document carefully and consider the ramifications of each limitation for your particular situation.
I’ve used this simple script to do some basic checks on a table before I enable it for versioning: check_table_for_owm.sql
Database schema changes made by OWM
Internally, when you version-enable a table (e.g.
MYTABLE), OWM makes a number of changes to your schema as follows:
MYTABLE_LT, for internal use only
- Adds a number of grants on the object to
- Adds some extra OWM-specific columns (with names prefixed with
WM_) and indexes to
- Creates the view
MYTABLE, for use by your application for querying and DML
- Creates an
INSTEAD OFtrigger (owned by
WMSYS) on the view to handle DML
- Creates some other triggers on any parent tables for referential integrity
- Creates one or two other tables (
MYTABLE_AUX, and sometimes
MYTABLE_LCK) for internal use only
- Creates some other views (
MYTABLE_MW) for application use where needed
- Converts any triggers and VPD policies on the table to metadata and then drops them; these are managed by OWM from then on
Based on the grants that OWM gives to
WMSYS (such as
ON COMMIT REFRESH and
QUERY REWRITE) I think the OWM tables are materialized views.
To change the structure of a version-enabled table, e.g. adding/modifying/removing a column, constraint, or trigger, the following steps must be done:
- Make the changes to a special table called
Note that this will fail if the table happens to have any constraints or indexes that are longer than 26 characters – so keep this in mind when naming them.
One of the most pleasing features of OWM is that it is relatively idiot-proof; if you try to perform an operation that is not supported, OWM will simply raise an exception (e.g. “
ORA-20061: versioned objects have to be version disabled before being dropped“) instead of leaving you with a mess to clean up. Therefore it’s generally safe to test your scripts by simply running them and seeing what happens. For example, try doing some damage to the underlying tables or views by changing columns or dropping them – OWM will stop you. I would never do this sort of experiment in a production environment, of course!
Column comments on a table that becomes version-enabled are not migrated automatically to the view. They do remain on the renamed table (
MYTABLE_LT). You can add comments to the view columns (and you don’t need to execute the
CommitDDL procedures for this); the same applies to the
TABLE_LT table as well. Note, however, that if you remove the versioning from the table the view is dropped along with its comments, so if you later re-enable versioning you might want to re-apply the column comments as well.
To copy/synchronize the column comments from the underlying
MYTABLE_LT table to the
MYTABLE view, I use this script: sync_comments_lt_to_view.sql.
Implementing Workspace Manager
These are some of the API calls that I’ve used to build OWM features into my application:
|set up a table for scenarios|
|create a scenario|
|view a scenario|
|create a snapshot|
|merge a scenario to its parent|
|drop a scenario|
|go back to live|
|compare two scenarios|
|analyze conflicts prior to merge|
“You just can’t tell about the future.”
APEX and VPD
My APEX application already uses VPD (or Row Level Security) to provide a multi-tenant environment for a wide range of users. Groups of users are assigned to one or more Security Groups; when they login they are assigned to one Security Group at a time which is set in a Global Application Context. A VPD policy has been applied to most tables like this:
function vpd_policy (object_schema in varchar2 ,object_name in varchar2 ) return varchar2 is begin return q'[security_group_id=sys_context('CTX','SECURITY_GROUP_ID')]'; end vpd_policy;
The Application Context is associated with the APEX session’s Client Identifier (e.g.
JBLOGGS:16630445499603) so each page request will be executed within the correct VPD context. A procedure is called from the Post-Authentication Procedure Name on the authentication scheme which sets the
SECURITY_GROUP_ID context variable.
For more info: Converting an APEX Application to Multi-Tenant
Workspace Manager and VPD
According to the docs, OWM can work along with VPD. However, you need to be aware of a few considerations:
- Row-level security policies are not enforced during workspace operations, including
- Row-level security policies must be defined on a number of views, not just the view for the version-enabled table.
- Don’t apply policies to the underlying tables created by OWM.
You can add VPD policies to a table prior to version-enabling it and these will be handled correctly by OWM when you version-enable it. However, if I need to add VPD policies after a table has been version-enabled, this is the code I run:
declare target_table varchar2(30) := 'MYTABLE'; begin for r in ( select view_name from user_views where view_name in (target_table ,target_table||'_LOCK' ,target_table||'_CONF' ,target_table||'_DIFF' ,target_table||'_HIST' ,target_table||'_MW') ) loop begin dbms_rls.add_policy (object_name => r.view_name ,policy_name => 'security_policy' ,policy_function => 'security_pkg.security_policy' ,update_check => true ,static_policy => true); exception when others then if sqlcode != -28101 /*policy already exists*/ then raise; end if; end; end loop; end;
It adds the policy to the base view, as well as the five specific associated views (
mytable_MW). Similar code may be used to alter or drop policies.
Note: with relation to VPD policies, the documentation doesn’t specifically mention the Multi-Workspace (
_MW) view. As far as I can tell, however, this is required – because when you version-enable a table, a VPD policy on a non-version-enabled table will be applied by Workspace Manager to this
_MWview as well as the other views.
Since a user’s changes within a workspace will be restricted to data for their Security Group, a
MergeWorkspace works just fine – even though technically the merge occurs across the entire table ignoring the VPD policy, the only changes we expect are those for the user’s Security Group.
However, Savepoints are different; like workspaces, they are a snapshot of the entire table; if you issue a
RollbackToSP, it will ignore any VPD policies and rollback all data to the selected savepoint. This means that you can’t use
RollbackToSP to give users the ability to rollback their data to a snapshot, while isolating their changes to their VPD context. For this reason I don’t use
RollbackToSP in my application.
All together – OWM, VPD and APEX
The users of my application need to be able to create scenarios for people in their Security Group to make proposed changes, see how the changes affect the reports, and merge those changes back to the “live” version. To facilitate this, I create a table to list the workspaces, and apply the VPD policy to it so that users can only view and work with workspaces for their Security Group.
create table scenarios ( scenario_id number default to_number(sys_guid() ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') not null ,scenario_name varchar2(200) not null ,wm_workspace varchar2(30) not null ,security_group_id number default sys_context('CTX','SECURITY_GROUP_ID') not null ,constraint scenario_pk primary key (scenario_id) ,constraint scenario_uk unique (wm_workspace) );
Each scenario has an internal ID (in this case, a surrogate key generated from a GUID), a user-specified name, and a name for the workspace. I didn’t use the user-entered name for the workspace name because (a) it must be unique across the database, and (b) it is limited to 30 characters.
After a user clicks the “Create Scenario” button and enters a name, the page process calls the following procedure (within the package
security_pkg associated with the context):
procedure create_scenario (scenario_name in varchar2 ,keep_refreshed in boolean := false) is l_wm_workspace varchar2(30); begin -- workspace name must be unique and &lt;=30 chars l_wm_workspace := to_basex (to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ,36); -- record the new scenario insert into scenarios (scenario_name, wm_workspace) values (create_scenario.scenario_name, l_wm_workspace); -- create the workspace dbms_wm.CreateWorkspace (workspace => l_wm_workspace ,isrefreshed => keep_refreshed ,description => scenario_name || ' (' || sys_context('CTX','SECURITY_GROUP') || ')' ,auto_commit => false); -- reset the savepoint, if required dbms_session.clear_context (namespace => 'CTX' ,attribute => 'WM_SAVEPOINT' ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); -- go to the workspace on the next page view dbms_session.set_context (namespace => 'CTX' ,attribute => 'WM_WORKSPACE' ,value => l_wm_workspace ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); end create_scenario;
to_basexfunction is based on code copied from this proposed OraOpenSource addition.
In my APEX application Security Attributes, I have the following:
|Initialization PL/SQL Code|
|Cleanup PL/SQL Code|
The procedures called above are as follows:
procedure wm_init is begin dbms_wm.GotoWorkspace(nvl(sys_context('CTX','WM_WORKSPACE') ,'LIVE')); dbms_wm.GotoSavepoint(nvl(sys_context('CTX','WM_SAVEPOINT') ,'LATEST')); end wm_init; procedure init_apex_session is begin wm_init; end init_apex_session; procedure cleanup_apex_session is begin dbms_wm.GotoWorkspace('LIVE'); dbms_wm.GotoSavepoint('LATEST'); end cleanup_apex_session;
The effect of this is that for each page request, the user’s selected workspace and/or savepoint is activated, or if they have not yet chosen a workspace or savepoint, the “live” workspace and “latest” savepoint is selected (which are the defaults). At the end of each page request, the session is reset to the “live” workspace and “latest” savepoint.
“That makes two of us.”
Create a Snapshot
Here is my code to create a snapshot, using the Workspace Manager SavePoint feature:
procedure create_snapshot (snapshot_name in varchar2) is l_wm_workspace varchar2(30); l_wm_savepoint varchar2(30); begin -- savepoint name must be unique and &lt;=30 chars l_wm_savepoint := to_basex (to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ,36); l_wm_workspace := nvl(sys_context(CTX,'WM_WORKSPACE'), 'LIVE'); -- record the snapshot insert into snapshots (snapshot_name ,wm_workspace ,wm_savepoint) values (create_snapshot.snapshot_name ,l_wm_workspace ,l_wm_savepoint); -- create the savepoint dbms_wm.CreateSavepoint (workspace => l_wm_workspace ,savepoint_name => l_wm_savepoint ,description => snapshot_name || ' (' || sys_context(CTX,'SECURITY_GROUP') || ')' ,auto_commit => false); end create_snapshot;
Go to a Scenario
This sets the context for the user’s session so that subsequent page requests will load the specified Workspace. Any DML the user performs on version-enabled tables will be private to the workspace.
procedure goto_scenario (scenario_name in varchar2) is l_wm_workspace varchar2(30); begin -- retrieve the workspace name for the given scenario select s.wm_workspace into l_wm_workspace from scenarios s where s.scenario_name = goto_scenario.scenario_name; -- reset the savepoint, if required dbms_session.clear_context (namespace => 'CTX' ,attribute => 'WM_SAVEPOINT' ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); -- go to the workspace on the next page view dbms_session.set_context (namespace => 'CTX' ,attribute => 'WM_WORKSPACE' ,value => l_wm_workspace ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); end goto_scenario;
View a Snapshot
This sets the context for the user’s session so that subsequent page requests will be viewing the specified Savepoint. The version-enabled tables will be read-only; any DML on them will raise an exception.
procedure goto_snapshot (snapshot_name in varchar2) is l_wm_workspace varchar2(30); l_wm_savepoint varchar2(30); begin -- retrieve the details for the given snapshot select s.wm_workspace ,s.wm_savepoint into l_wm_workspace ,l_wm_savepoint from snapshots s where s.snapshot_name = goto_snapshot.snapshot_name; -- set the workspace and savepoint on the next page request dbms_session.set_context (namespace => 'CTX' ,attribute => 'WM_WORKSPACE' ,value => l_wm_workspace ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); dbms_session.set_context (namespace => 'CTX' ,attribute => 'WM_SAVEPOINT' ,value => l_wm_savepoint ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); end goto_snapshot;
Go to Live
When the user is in a Scenario, and they wish to go back to “Live”, they can click a button which executes the following procedure. Their next page request will go to the LATEST savepoint in the LIVE workspace.
procedure goto_live is begin dbms_session.clear_context (namespace => 'CTX' ,attribute => 'WM_WORKSPACE' ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); dbms_session.clear_context (namespace => 'CTX' ,attribute => 'WM_SAVEPOINT' ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); end goto_live;
Go to Latest
When the user is viewing a Snapshot, and they wish to go back to “Latest” (so they do DML, etc.), they can click a button which executes the following procedure. This works regardless of whether they are in the Live workspace or viewing a scenario. Their next page request will go to the LATEST savepoint.
procedure goto_latest is begin dbms_session.clear_context (namespace => 'CTX' ,attribute => 'WM_SAVEPOINT' ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); end goto_latest;
Compare two Scenarios
When the user wishes to analyze the differences between two scenarios, or between a scenario and the Live workspace, there is a two-step process:
- The user selects two Scenarios (or the “Live” workspace) from some select lists; the return value of these select lists should be the
wm_workspacecolumn from the table, or
'LIVE'for the Live workspace.
- The user clicks a button to execute the
find_diffs1procedure (see below).
- The page has a report on the view
procedure find_diffs1 (wm_workspace1 in varchar2 ,wm_workspace2 in varchar2) is begin dbms_wm.SetDiffVersions (workspace1 => wm_workspace1 ,workspace2 => wm_workspace2 ,onlyModified => true); end find_diffs1;
When the user wishes to analyze the differences between two snapshots (in any workspace), or between the latest data and a snapshot, a similar process is used:
- The user selects two Snapshots (or the “Latest” snapshot for either the Live workspace or a given Scenario); the return values of these select lists should be the
- The user clicks a button to execute the
find_diffs2procedure (see below).
- The page has a report on the view
procedure find_diffs (workspace_savepoint1 in varchar2 ,workspace_savepoint2 in varchar2) is l_sep1 number := instr(workspace_savepoint1,':'); l_sep2 number := instr(workspace_savepoint2,':'); begin dbms_wm.SetDiffVersions (workspace1 => substr(workspace_savepoint1, 1, l_sep1-1) ,savepoint1 => substr(workspace_savepoint1, l_sep1+1) ,workspace2 => substr(workspace_savepoint2, 1, l_sep2-1) ,savepoint2 => substr(workspace_savepoint2, l_sep2+1) ,onlyModified => true); end find_diffs;
Merge a Scenario
In my system, I don’t allow users to create scenarios within scenarios, although this is possible with Workspace Manager. If the user wishes to merge all changes in a Scenario to Live, they click a button to execute the following procedure:
wm_error_55 exception; wm_error_56 exception; pragma exception_init(wm_error_55, -20055); pragma exception_init(wm_error_56, -20056); procedure merge_scenario is l_wm_workspace varchar2(30); begin l_wm_workspace := sys_context('CTX','WM_WORKSPACE'); goto_live; wm_init; -- merge the workspace dbms_wm.MergeWorkspace (workspace => l_wm_workspace ,remove_workspace => true ,auto_commit => false); -- delete the scenario delete scenarios s where s.wm_workspace = merge_scenario.scenario_id; exception when wm_error_55 or wm_error_56 then -- unable to merge due to conflicts -- go back into the workspace dbms_session.set_context (namespace => 'CTX' ,attribute => 'WM_WORKSPACE' ,value => l_wm_workspace ,client_id => sys_context('userenv','CLIENT_IDENTIFIER')); -- caller should redirect user to the "resolve conflicts" page raise e_merge_conflict; end merge_scenario;
This will fail with an exception if there are any conflicting changes in the Live workspace, e.g.:
- a record was updated in the scenario, but was deleted in Live
- a record was updated or deleted in the scenario, but also updated in Live
- a record was inserted in the scenario, but another record with the same PK was inserted in Live
If the workspace had been created with the
isrefreshed option, the changes being made in Live will be automatically copied to the workspace so these conflicts should be minimised; however, conflicts cannot always be avoided. To give the user the ability to analyze the conflicts, you would perform the following steps:
dbms_wm.BeginResolve(workspace => sys_context('CTX','WM_WORKSPACE'))
- Show a report that queries the view
- Allow the user to select an action for each record in conflict – either “PARENT” (keep the parent, i.e. discard the change in the scenario) or “CHILD” (keep the child, i.e. discard the change made in Live).
- For each record, call
dbms_wm.ResolveConflicts(see example below).
dbms_wm.CommitResolve(workspace => sys_context('CTX','WM_WORKSPACE'))
ResolveConflicts procedure takes a
where clause that identifies the row (or rows) to mark as resolved. In my case, I just call it with the
id for each record the user chose:
for r in ( ...query on something, e.g. an APEX collection... ) loop dbms_wm.ResolveConflicts (workspace => sys_context('CTX','WM_WORKSPACE') ,table_name => 'MYTABLE' ,where_clause => 'id=' || r.id ,keep => r.action /*'PARENT' or 'CHILD'*/); end loop;
If the conflicts are across multiple tables, the UI will probably be a bit more complicated. You’d have to resolve conflicts on all the affected tables before the Merge can succeed.
“Don’t let a white fence and a promotion end the world for you.”
Delete a Scenario
Deleting a scenario uses
procedure delete_scenario (scenario_name in varchar2) is l_wm_workspace varchar2(30); begin -- retrieve the workspace name for the given scenario select s.wm_workspace into l_wm_workspace from scenarios s where s.scenario_name = delete_scenario.scenario_name; -- get out of the workspace goto_live; wm_init; -- delete the workspace dbms_wm.RemoveWorkspace (workspace => l_wm_workspace ,auto_commit => false); delete scenarios s where s.wm_workspace = l_wm_workspace; end delete_scenario;
Delete a Snapshot
Deleting a snapshot uses
procedure delete_snapshot (snapshot_name varchar2) is l_wm_workspace varchar2(30); l_wm_savepoint varchar2(30); begin -- retrieve the details for the given snapshot select s.wm_workspace ,s.wm_savepoint into l_wm_workspace ,l_wm_savepoint from snapshots s where s.snapshot_name = delete_snapshot.snapshot_name; -- get out of the snapshot/scenario: goto_latest; wm_init; -- delete the savepoint dbms_wm.DeleteSavepoint (workspace => nvl(l_wm_workspace,'LIVE') ,savepoint_name => l_wm_savepoint ,auto_commit => false); delete snapshots s where s.wm_savepoint = l_wm_savepoint; end delete_snapshot;
One of the requirements of my application was to show a report of the entire history of edits to each record in a table. Since I’m already going to version-enable this table, it makes sense to take advantage of the Row History feature of Oracle Workspace Manager.
When you version-enable a table, OWM creates a view called
MYTABLE_HIST which includes all the columns of the table, plus the following columns:
WM_RETIRETIME. By default, when you version-enable a table, OWM keeps only a minimal set of history in order to support other features in use, such as snapshots. In order to retain a complete history of changes to the record, enable versioning with the View Without Overwrite history option:
begin dbms_wm.EnableVersioning('MYTABLE' ,hist => 'VIEW_WO_OVERWRITE'); end;
This stops OWM from overwriting the history of changes to each record, so it can be queried via the
Now, I wanted to expose the contents of this view to my users, and for each history record show Who did it and When. “When” is easily answered by
WM_CREATETIME, which is a
TIMESTAMP WITH TIME ZONE. “Who” would normally be answered by
WM_USERNAME, but since we’re in APEX, this will always be
'APEX_PUBLIC_USER' which is not very useful. Therefore, I have an ordinary column in my table called
DB$UPDATED_BY which is set by the following ordinary trigger:
create trigger MYTABLE$TRG before update on MYTABLE for each row begin :new.db$updated_by := coalesce(sys_context('APEX$SESSION','APP_USER') ,sys_context('USERENV','SESSION_USER')); end MYTABLE$TRG;
This means my report will show the actual APEX username of who created or updated the record.
WM_OPTYPE column in the
_HIST view will be
'U' (updated), or
'D' (deleted). I can translate this code into user-friendly values for reporting purposes, but there is one problem: when a row is deleted, the
_HIST table knows the username who deleted the record, but we never recorded the APEX username. Our
db$updated_by column wasn’t changed, so it simply shows whoever last updated the records before it was deleted.
To solve this, we have two options:
(1) Write the application to do a quick no-change UPDATE on each row immediately before deleting it; then do some fancy footwork in the view to show this as a “Delete” operation.
(2) Don’t allow the application to execute actual
DELETEs – in other words, use a “soft-delete” design.
I’ve gone with option (2) which is what the users needed anyway – they needed to be able to Delete and Undelete records at will, so I’ve only granted INSERT and UPDATE on the table and the application will translate a request to “DELETE” into an update of a “deleted” flag on the table. A request to Undelete a record is simply another update to set the “deleted” flag to NULL. All reports in the application have a predicate to filter out any deleted records unless the user is viewing the “recycle bin”. In addition, if the user is viewing a deleted record, I put the form into “Readonly” mode – the only thing they can do is Undelete it (thereafter, they can modify the record if they wish).
I’ve created the following view in order to show the history of changes to a record:
create or replace view mytable_hist_vw as select x.id, x.name, etc. ,l.wm_createtime as op_timestamp ,case when x.wm_optype = 'U' and x.deleted_ind = 'Y' and lag(x.deleted_ind,1) over (partition by x.id order by x.wm_createtime) is null then 'Deleted' when x.wm_optype = 'U' and x.deleted_ind is null and lag(x.deleted_ind,1) over (partition by x.id order by x.wm_createtime) = 'Y' then 'Undeleted' when x.wm_optype = 'I' then 'Created' when x.wm_optype = 'U' then 'Updated' when x.wm_optype = 'D' then 'Deleted permanently' end as op_desc ,case when x.wm_optype = 'D' then x.wm_username else x.db$updated_by end as op_by from mytable_hist x
I interpret an
'D' as “Deleted permanently”. This might occur if we do a delete directly on the table, e.g. via a data fix run directly on the database. In this case I report the username according to the
wm_username column since we won’t have the APEX user name.
Data Dictionary Views
OWM provides a number of data dictionary views:
|Constraints on version-enabled tables|
|Triggers on version-enabled tables|
|VPD policies on version-enabled tables|
According to Bill Beauregard (Long Transactions with Oracle Database Workspace Manager Feature), we can expect to see some enhancements to OWM in the near future, including:
- 128 character workspace names
- Relaxed limitations on table, column and index name length
- Support for invisible indexes, virtual and identity columns
- Oracle: Long Transactions with Oracle Database Workspace Manager Feature by Bill Beauregard
- Youtube: Git Out of Town: Version Control for Data by Dan McGhan
- Oracle-Base: Workspace Management in Oracle9i
- GitHub: OraOpenSource: Transaction Number Generator
- Intro to Workspace Manager
- Workspace Manager Static Data Dictionary Views
- Workspace Manager Errors Reference
- Virtual Private Database
Did you recognise all the TV shows referenced in this article?