Flashback Version and Flashback Transaction Query

Flashback technology offers us the chance to traverse the undo tablespace for detailed version information of data changes within the database.

Flashback version is particularly useful for investigating complex applications covering a labyrinth of subroutines and conditions.
Below is a basic example of its use.

--PREP WORK
drop table ray.employee;
create table ray.employee (
  id       number,
  name     varchar2(60),
  position varchar2(20)
  );


insert into ray.employee VALUES (1,'TOPPER HARLEY','TOP GUN');
commit;

set lines 1000



select * from ray.employee;
        ID NAME                                                         POSITION
---------- ------------------------------------------------------------ --------------------
         1 TOPPER HARLEY                                                TOP GUN


update ray.employee set name = 'SPUD WESTERN';

commit;

update ray.employee set name = 'JOHN MCWHEELY';

commit;

update ray.employee set name = 'DUKE SPENCER', position='PILOT';

commit;

delete from ray.employee;

commit;



--View the record versions
set lines 1000
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select TO_CHAR(VERSIONS_STARTTIME,'DD-MON-YY HH24:MI:SS') "start", 
       TO_CHAR(VERSIONS_ENDTIME,'DD-MON-YY HH24:MI:SS') "end", 
       versions_operation, 
       versions_xid,
       t.*
  from ray.employee versions between scn minvalue and maxvalue t 
 where id = 1;

start                end                  VE VERSIONS_XID       ID NAME                 POSITION
-------------------- -------------------- -- ---------------- ---- -------------------- --------------------
12-JAN-12 09:16:22                        D  0900180032010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:22   U  080013002F010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:13   U  07001C0038010000    1 JOHN MCWHEELY        TOP GUN
12-JAN-12 09:15:58   12-JAN-12 09:16:13   U  0600160035010000    1 SPUD WESTERN         TOP GUN
                     12-JAN-12 09:15:58                          1 TOPPER HARLEY        TOP GUN

--Above, the start time and scn of the record version are given to use as well as the data of that version. We also see the type of transaction which generated the version (U/D). We can look as far back as the undo tablespace size and retention period allow us to.
--Using FLASHBACK QUERY with VERSION 
--For more transaction related information, we can use FLASHBACK_TRANSACTION_QUERY view to see details about the transaction.
The privilege FLASHBACK ANY TABLE must be given to the person doing the selecting.



COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN LOGON_USER FORMAT a10
COLUMN UNDO_SQL FORMAT a10
COLUMN TABLE_NAME FORMAT a10
COLUMN TABLE_OWNER FORMAT a10
COLUMN XID FORMAT a20
COLUMN ROWID FORMAT a20
COLUMN OPERATION FORMAT a20
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select * 
  from flashback_transaction_query where xid in (
select VERSIONS_XID
  from ray.employee versions between scn minvalue and maxvalue r 
 where versions_operation = 'U');

XID                   START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION            TABLE_NAME TABLE_OWNE ROW_ID              UNDO_SQL
-------------------- ---------- --------- ---------- --------- ---------- ------------ -------------------- ---------- ---------- ------------------- ----------
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   2 BEGIN
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   2 BEGIN
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   2 BEGIN