Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 25 June 2012

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('2012-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('2012-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('2012-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS')  
AND TO_TIMESTAMP('2012-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('2012-06-24 07:20:00', 'YYYY-MM-DD HH:MI:SS') AND
  TO_TIMESTAMP('2012-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. For the reference of example check out this link: http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

0 comments:

Post a Comment