|
To Find changed values in a table with flashback The as of clause in a select statement allows us to see the data as of a past 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 am trying to explain here.
Create the following test table to give a practical try:
create table flashback_testing (
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. As most of the times we
will face the following error in Oracle reports "ORA-01466 unable to read data -
table definition has changed". I want to prevent that:
exec dbms_lock.sleep(5*60 + 1)
Inserting some records:
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;
Lets create an bind variable
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
A few records are updated:
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, we will full join the values as of
now with the values that were valid at the SCN stored in the variable :v_scn.
The where condition now have to show the records that were changed.
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
Now you can see the only values that were updated.
|
