Use of Oracle Flashback Version/Oracle Flashback Transactional Query

Consider the example when you are notice that the record payslip_number=21707 is missing from the HRMS.Pay_payment_detail table.

SELECT payslip_number, alw_ded_code, amount 

FROM pay_payment_detail where payslip_number=21707;

PAYSLIP_NUMBER ALW_     AMOUNT

-------------- ---- ----------

         21707 A010       2250

         21707 A020        375

         21707 A040        250

         21707 D016         35

         21707 G010        180

Delete  from pay_payment_detail

where payslip_number=21707;
commit;

By using oracle flash back query you can retrieves data as it existed at earlier time. The query explicitly references the past time through timestamp or SCN. It returns committed data that was current at that point in time.

For Example: Retrieving a lost row with oracle flashback query

SELECT payslip_number, alw_ded_code, amount 

FROM pay_payment_detail

AS OF TIMESTAMP  TO_TIMESTAMP('2013-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS')

 WHERE payslip_number = 21707;

PAYSLIP_NUMBER ALW_     AMOUNT

-------------- ---- ----------

         21707 A010       2250

         21707 A020        375

         21707 A040        250

         21707 D016         35

         21707 G010        180

INSERT INTO pay_payment_detail

  (SELECT * FROM pay_payment_detail

     AS OF TIMESTAMP

       TO_TIMESTAMP('2013-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS')

        WHERE payslip_number = 21707);

Check and commit the record after querying it.

Select * from pay_payment_detail

where payslip_number=21707;

PAYSLIP_NUMBER PAYMENT_TYPE ALW_DED_FLAG ALW_     AMOUNT   PRN_FLAG PRN_AMOUNT

-------------- ------------ ------------ ---- ---------- ---------- ----------

         21707            1            1 A010       2250          1          0

         21707            1            1 A020        375          1          0

         21707            1            1 A040        250          1          0

         21707            4            2 D016         35          1          0

         21707            1            2 G010        180          1          0

Then after you need to identify the following with Oracle Flashback Version query

The transaction identifier of the transaction that deleted the payslip_no record

The SQL statements necessary to undo the delete

The user who executed the transaction

SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, payslip_number, amount 

FROM pay_payment_detail

VERSIONS BETWEEN TIMESTAMP 

TO_TIMESTAMP('‎2013-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS‎')  

AND TO_TIMESTAMP('‎2013-06-24 07:35:00', 'YYYY-MM-DD HH:MI:SS‎')

WHERE payslip_number = ‎21707‎;

SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW('000200030000002D');

To find the user id who is responsible for this change or delete

SELECT xid, logon_user

FROM flashback_transaction_query

WHERE xid IN (

  SELECT versions_xid FROM pay_payment_detail VERSIONS BETWEEN TIMESTAMP

  TO_TIMESTAMP('2013-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS') AND

  TO_TIMESTAMP('2013-06-24 07:35:00', 'YYYY-MM-DD HH:MI:SS'));

Note: You need to use Oracle Flashback Transaction Query with Oracle Flashback Version Query in the situation when two similar row of the same table is delete on different interval..