Author: R. Nyffenegger
In this article I will discuss some great tips about Oracle flashback. You can use the as of clause in a select statement to see the data as of a past SCN or past point in time. This makes it possible to compare the current data in a table with a previous data set.
Comparing Data from two different times:
You can compare data from two different times by using the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS . The results can be stored by preceding a Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement.
Below query re-inserts the rows that were present an hour ago in the table.
INSERT INTO employee
(SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘60′ MINUTE))
MINUS SELECT * FROM employee);
Creating View:
A view referring to past data can be created by using the AS OF clause in the SELECT statement that defines the view. The past time is recalculated for each query if you specify a relative time by subtracting from SYSDATE . After a change in daylight savings time, SYSDATE – 1 might refer to either 23 or 25 hours ago instead of 24.
CREATE VIEW hour_ago AS
SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘60′ MINUTE );
SCN vs. TIMESTAMP:
Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. Use an SCN instead of a timestamp if a possible 3-second error (maximum) is important to a Flashback Query in your application.
Flashback for DDL and DML Operations:
The AS OF clause for each table can be specified or omitted and different times for different tables can be specified. AS OF clause can be used in a query to perform DDL operations such as creating and truncating tables or DML operations such as inserting and deleting) in the same session as the query.
AS OF clause can be used inside an INSERT or CREATE TABLE AS SELECT statement in order to use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database.
Finding changed values in a table:
Consider a table say flashback_ex. If you apply the as of clause on a table within the first five minutes after its creation then Oracle reports an ORA-01466 unable to read data – table definition has changed error .
create table flashback_ex (
id number not null,
txt varchar2(7)
);
exec dbms_lock .sleep(5*60 + 1)
Now we insert some records into above table.
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;
At this stage a bind variable is created and assigned the current SCN. The value of this variable could now be printed with print v_scn .
variable v_scn number
… exec :v_scn := dbms_flashback .get_system_change_number
Now we will update a few records
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;
Now in order to find changed values in the table use full join on the values as of now with the values that were valid at the SCN stored in the variable v_scn . The where conditions makes sure only those records are returned that have 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
