We've got an application which lets users create workspaces for "jobs", where the USER_WORKSPACES.WORKSPACE attribute is recorded in the job table in our application schema. The tables have been versioned with hist=>VIEW_WO_OVERWRITE.
When a workspace is created for a user, they can insert, update and delete rows in the versioned tables. When they're done, the workspace is merged back into LIVE with remove_workspace=>TRUE.
In the *_HIST view, the WM_OPTYPE, WM_CREATETIME & WM_RETIRETIME attributes provide a nice audit trail of what was deleted. In addition to that information, we'd now like to know the USER_WORKSPACES.WORKSPACE or even USER_WORKSPACES.WORKSPACE_ID of the workspace in which the deletion was done, so we can link it back to the job in our application schema. Prior to removing the workspace, that information is available through the HIST view and also through ALL_VERSION_HVIEW, but once the workspace is removed, that information seems to be lost.
SQL> -- Setup
SQL> create table t1 (id integer primary key, name varchar2(5));
Table created.
SQL> execute dbms_wm.enableVersioning(table_name=>'T1',hist=>'VIEW_WO_OVERWRITE');
PL/SQL procedure successfully completed.
SQL> insert into t1 (id, name) values (1, 'one');
1 row created.
SQL> commit;
Commit complete.
SQL> execute dbms_wm.createWorkspace('ws1');
PL/SQL procedure successfully completed.
SQL> select workspace_id, workspace from user_workspaces;
WORKSPACE_ID WORKSPACE
------------ ----------
238 ws1
1 row selected.
SQL> -- Go to workspace ws1 and delete the row
SQL> execute dbms_wm.goToWorkspace('ws1');
PL/SQL procedure successfully completed.
SQL> delete t1 where id = 1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> -- At this pre-merge stage, the HIST view has the 'ws1' value for WM_WORKSPACE
SQL> select id, name, wm_workspace, wm_version, wm_username, wm_optype
2 from t1_hist
3 order by wm_createtime;
ID NAME WM_WORKSPACE WM_VERSION WM_USERNAME WM_OPTYPE
---------- ----- ------------ ---------- ------------ ---------
1 one LIVE 0 JOT_TEST I
1 one ws1 142 JOT_TEST D
2 rows selected.
SQL> -- ALL_VERSION_HVIEW tells me that WM_VERSION 142 was made in WM_WORKSPACE 'ws1'
SQL> select *
2 from all_version_hview;
VERSION PARENT_VERSION WORKSPACE WORKSPACE_ID
---------- -------------- ---------- ------------
0 -1 LIVE 0
141 0 LIVE 0
142 0 ws1 238
3 rows selected.
SQL> execute dbms_wm.goToWorkspace('LIVE');
PL/SQL procedure successfully completed.
SQL> execute dbms_wm.mergeWorkspace('ws1');
PL/SQL procedure successfully completed.
SQL> -- Afer mergin, the HIST view still has the 'ws1' value for WM_WORKSPACE
SQL> select id, name, wm_workspace, wm_version, wm_username, wm_optype
2 from t1_hist
3 order by wm_createtime;
ID NAME WM_WORKSPACE WM_VERSION WM_USERNAME WM_OPTYPE
---------- ----- ------------ ---------- ------------ ---------
1 one LIVE 0 JOT_TEST I
1 one ws1 142 JOT_TEST D
1 one LIVE 141 JOT_TEST D
3 rows selected.
SQL> -- And ALL_VERSION_HVIEW still tells me that WM_VERSION 142 was made in WM_WORKSPACE 'ws1'
SQL> select *
2 from all_version_hview;
VERSION PARENT_VERSION WORKSPACE WORKSPACE_ID
---------- -------------- ---------- ------------
0 -1 LIVE 0
141 0 LIVE 0
142 0 ws1 238
143 142 ws1 238
4 rows selected.
SQL> -- But once the workspace is removed, I no longer have a record of ws1 in the HIST view
SQL> execute dbms_wm.removeWorkspace('ws1');
PL/SQL procedure successfully completed.
SQL> select id, name, wm_workspace, wm_version, wm_username, wm_optype
2 from t1_hist
3 order by wm_createtime;
ID NAME WM_WORKSPACE WM_VERSION WM_USERNAME WM_OPTYPE
---------- ----- ------------ ---------- ------------ ---------
1 one LIVE 0 JOT_TEST I
1 one LIVE 0 JOT_TEST D
2 rows selected.
SQL> -- And ALL_VERSION_HVIEW no longer has the information about 'ws1'
SQL> select *
2 from all_version_hview;
VERSION PARENT_VERSION WORKSPACE WORKSPACE_ID
---------- -------------- ---------- ------------
0 -1 LIVE 0
So in this case, up until the point where the workspace was merged, I was able to tell that ws1 was the workspace which had deleted the row in t1. But that information is lost when we remove the workspace.
Is this just the way workspace manager works, or is there some other way of retaining this information post-workspace removal?