I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know. Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much. Which is better for big tables, with million(s) rows please? Thank you all.
If flashback does not work for you for some reason, you could add a column to your table to save the original value of the indicator and a trigger of the sort:
create trigger t before update of ind_col on your_table for each row begin if :old.ind_save_col = '' then :new.ind_save_col := :old.ind_col; end if; end; /
(code not tested). Your nightly process would then copy rows where
ind_col != ind_save_col, then reset