2 Replies Latest reply: Jul 24, 2003 7:59 AM by 171567 RSS

    recovering changes maded in a versioned table

    171567
      Dear People,
      I need to recover the changes maded in a versioned table. I mean, i need the following structure:

      modified_field | old_value | new_value | date_of_change
      field_1 | 12 | 58 | 05/03/2003
      field_2 | 'Jane' | 'Sarah' | 06/09/2003

      the HIST view show the same information but in other order, and i don't know how to build a query with the information from the HIST view that give me the result i need. Maybe there's another view or table that a can use to do this...

      Thanks in advance.
      Jimena.
        • 1. Re: recovering changes maded in a versioned table
          141424
          Jimena,
          Workspace Manager tracks changes at the row-level (not at the column-level). So, you will be able to write a query (as shown below) based on the _hist view that will show changes to data in a versioned table at the row level in the structure that you wanted(not at the column level as your example indicated).

          regards
          Ramesh.

          The following SQL shows an example of such a query:
          -- Create the test table
          create table testTable (
          id integer primary key,
          field1 integer,
          field2 varchar2(30)
          );

          -- Populate the test table
          insert into testTable values (1, 100, 'Jane');
          insert into testTable values (2, 200, 'John');
          insert into testTable values (3, 300, 'Joe');
          commit;

          exec dbms_wm.enableVersioning('testTable','VIEW_WO_OVERWRITE');

          -- Create a view that will show changes as requested
          create or replace view testTable_changes as
          select hist1.id,
          hist1.field1 old_field1, hist2.field1 new_field1,
          hist1.field2 old_field2, hist2.field2 new_field2,
          hist2.wm_workspace, hist2.wm_createtime date_of_change
          from testTable_hist hist1, testTable_hist hist2
          where hist1.id = hist2.id and hist1.wm_retiretime = hist2.wm_createtime;


          /* Now update different fields for ID=1,2(not ID=3)*/
          update testTable set field2 = 'Sarah' where id = 1;
          update testTable set field1 = 999 where id = 2;
          commit;

          -- Now, select from the views
          select * from testTable;
          /*
          ID FIELD1 FIELD2
          --- ------ ------
          1 100 Sarah
          2 999 John
          3 300 Joe
          */

          /* Only rows that changed will be shown - i.e. ID= 1,2 */
          select * from testTable_changes;
          /*
          ID OLD_FIELD1 NEW_FIELD1 OLD_FIELD2 NEW_FIELD2 WM_WORKS DATE_OF_CHANGE
          -- ---------- ---------- ---------- ---------- -------- -----------------------------------
          1 100     100 Jane     Sarah LIVE     23-JUL-03 02.49.04.298387 PM -04:00
          2 200     999 John     John LIVE     23-JUL-03 02.49.04.446936 PM -04:00
          */
          • 2. Re: recovering changes maded in a versioned table
            171567
            Thanks a lot Ramesh!!!