Trigger and temp table or materialized view 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 ind_save_col.