December 31, 2006: On finding changed values in a table with flashback

The as of clause in a select statement allows to see the data as of a past SCN or past point in time. So, it's possible, for example, to compare the current data in a table with a previous data set. This is what I try to do.

create table flashback_ex (
  id  number       not null,
  txt varchar2(7)
);

For a reason I don't really understand, the as of clause cannot be used on a table within the first five minutes after its creation. Otherwise, Oracle reports an ORA-01466 unable to read data - table definition has changed. I want to prevent that:

exec dbms_lock.sleep(5*60 + 1)

insert into flashback_ex values (1,'one'  );
insert into flashback_ex values (2,'two'  );
insert into flashback_ex values (3,'three');
insert into flashback_ex values (4,'four' );
insert into flashback_ex values (5,'five' );
insert into flashback_ex values (6,'six'  );
insert into flashback_ex values (7,'seven');
insert into flashback_ex values (8,'eight');
insert into flashback_ex values (9,'nine' );

commit;

variable v_scn number

... and assigned the current SCN. (The value of this variable could now be printed with print v_scn.)

exec :v_scn := dbms_flashback.get_system_change_number

insert into flashback_ex values (10, 'ten'   );
insert into flashback_ex values (20, 'twenty');

update flashback_ex set id = 40, txt='fourty' where id = 4; 
update flashback_ex set id = 50, txt='fifty'  where id = 5; 

delete from flashback_ex where id = 7;
delete from flashback_ex where id = 8;

commit;

In order to find changed values in the table, I full join the values as of now with the values that were valid at the SCN stored in the variable :v_scn.

set numf 99999

select
  case when prv_id is null then 'deleted' 
       when cur_id is null then 'inserted'
       else                     'updated'
  end                                      operation,
  prv_id,  cur_id,
  prv_txt, cur_txt
from (
  select rowid r, id prv_id, txt prv_txt from flashback_ex 
         as of scn :v_scn
) full join (
  select rowid r, id cur_id, txt cur_txt from flashback_ex
) using (r)
where prv_id  != cur_id   or
      prv_txt != cur_txt  or
      prv_id  is null     or
      cur_id  is null;

OPERATIO PRV_ID CUR_ID PRV_TX CUR_TX
-------- ------ ------ ------ ------
updated       4     40 four   fourty
updated       5     50 five   fifty
inserted      8        eight
inserted      7        seven
deleted             20        twenty
deleted             10        ten


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger

December 31, 2006: On finding changed values in a table with flashback

More on Oracle