Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Identify the workspace which deleted a row historically

John OToole (Dublin)Aug 13 2018 — edited Aug 30 2018

Oracle 12.1.0.2

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.

Here's an example:

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?

Thanks,

John

This post has been answered by Ben Speckhard-Oracle on Aug 29 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 27 2018
Added on Aug 13 2018
2 comments
447 views