Using Flashback Query

-- Based on: "Oracle Flash Back Query Explained with Example", oracle-dba-online.com – https://www.oracle-dba-online.com/flash_back_features.htm

-----------------------------------------------------------------------------------------------------------------------
-- Using Flashback Version Query
-----------------------------------------------------------------------------------------------------------------------
VERSIONS_XID                :Identifier of the transaction that created the row version
VERSIONS_OPERATION          :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN           :Starting System Change Number when the row version was created
VERSIONS_STARTTIME          :Starting System Change Time when the row version was created
VERSIONS_ENDSCN             :SCN when the row version expired.
VERSIONS_ENDTIME            :Timestamp when the row version expired

-- Before Starting this example let’s us collect the Timestamp
select to_char(SYSTIMESTAMP,’YYYY-MM-DD HH:MI:SS’) from dual;

Create table emp (empno number(5),
                  name varchar2(20),
                  sal number(10,2));

-- Suppose a user creates a emp table and inserts a row into it and commits the row.
insert into emp values (101,’Sami’,5000);
commit;

-- Now a user sitting at another machine erroneously changes the Salary from 5000 to 2000 using Update statement
update emp set sal=sal-3000 where empno=101;
commit;

-- Subsequently, a  new transaction updates the name of the employee from Sami to Smith.
update emp set name=’Smith’ where empno=101;
commit;

-- The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 101.
select versions_xid,versions_starttime,versions_endtime,
       versions_operation,empno,name,sal 
  from emp versions between timestamp to_timestamp('2016-09-01 20:30:00','yyyy-mm-dd hh:mi:ss') 
                            and to_timestamp('2016-09-10 21:00:00','yyyy-mm-dd hh:mi:ss');

VERSION_XID  V STARTSCN  ENDSCN  EMPNO  NAME       SAL
-----------  - --------  ------  -----  --------   ----
0200100020D  U 11323                101  SMITH     2000       
02001003C02  U 11345                101  SAMI      2000
0002302C03A  I 12320                101  SAMI      5000

-- use the VERSION_XID from orev statement 
-- The DBA identifies the transaction 02001003C02 as erroneous and issues the following query to get the SQL command to undo the change
select operation,logon_user,undo_sql 
  from flashback_transaction_query 
 where xid=HEXTORAW('02001003C02');

OPERATION  LOGON_USER UNDO_SQL
---------  ---------- -------------------------------------------------------------
U           SCOTT       update emp set sal=5000 where ROWID =  'AAAKD2AABAAAJ29AAA'

-- Now DBA can execute the command to undo the changes made by the user
update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA';
commit;