Flashback transaction Query

• Principle: Based on undo
• A flashback transaction query can be viewed as a diagnostic tool that queries database changes at the transaction level
• Flashback_transaction_query
  • Retrieve transaction information for all tables involved in a transaction.
  • Provides the SQL statements that's can use to undo The changes made by a particular transaction
• Need to open the minimum additional log:
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Cases:

 
   
  

  
    
   
\**查询所有事务信息*\
  
    
   
SQL> SELECT operation, undo_sql, table_name FROM flashback_transaction_query;
 
   
  

  
 

   
  

  
\**返回指定事务的信息*\
   
  

  
SQL> SELECT operation, undo_sql, table_name FROM flashback_transaction_query WHERE xid = HEXTORAW(‘8C0024003A000000‘) ORDER BY undo_change#;
   
  

  

   
  

  
SELECT xid, 
   
  

  
 operation, 
   
  

  
 start_scn, 
   
  

  
 commit_scn, 
   
  

  
 logon_user, 
   
  

  
 undo_sql
   
  

  
FROM flashback_transaction_query
   
  

  
WHERE xid = HEXTORAW(‘000400070000004F‘);
  
 

  
 

   
  

  
\**返回指定时间间隔的事务信息*\
   
  

  
SQL> SELECT operation, undo_sql, table_name FROM flashback_transaction_query WHERE start_timestamp >= TO_TIMESTAMP (‘2003-10-21 11:00:00‘,‘YYYY-MM-DD HH:MI:SS‘)
   
  

  
AND commit_timestamp <= TO_TIMESTAMP(‘2003-10-21 11:30:00‘,‘YYYY-MM-DD HH:MI:SS‘);
   
  

  

   
  

  
SELECT xid, 
   
  

  
logon_user 
   
  

  
FROM flashback_transaction_query 
   
  

  
WHERE xid IN ( SELECT versions_xid
   
  

  
FROM personnel 
   
  

  
VERSIONS BETWEEN 
   
  

  
TIMESTAMP TO_TIMESTAMP(‘2007-03-21 19:30:00‘, ‘YYYY-MM-DD HH24:MI:SS‘)
   
  

  
AND TO_TIMESTAMP(‘2007-03-22 04:30:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) );
  
 

Real-Operating scenes

1. Open additional logs and authorize

 
   
  

  
    
   
alter database add supplemental log data; 
  
    
   
grant SELECT ANY TRANSACTION to hr;
 
   
  

2. Generating transactions

  
 

   
  

  
insert into hr.departments 
   
  

  
(department_id,department_name,manager_id,location_id) 
   
  

  
values (999,‘SETI‘,100,1700); 
   
  

  

   
  

  
update hr.employees set department_id=999 
   
  

  
where employee_id=200; 
   
  

  

   
  

  
commit;
  
 

3. Get the transaction number and get the undo SQL by the transaction number

  
 

   
  

  
select 
   
  

  
versions_xid,versions_startscn,department_id,department_name 
   
  

  
from hr.departments 
   
  

  
versions between timestamp minvalue and maxvalue 
   
  

  
where department_id=999 
   
  

  
order by 2 nulls first; 
   
  

  

   
  

  
set line 200
   
  

  
col undo_sql for a90
   
  

  
SELECT operation, undo_sql, table_name FROM flashback_transaction_query WHERE xid = HEXTORAW(‘10000A0039030000‘) ORDER BY undo_change#;
   
  

  
OPERATION UNDO_SQL TABLE_NAME
   
  

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

  
  update UPDATE  span class= "str" > "HR"   "EMPLOYEES"    set     department_id "   =     '    where   ROWID  =     ' AAAWXEAAEAAAAZTAAC '  ;   EMPLOYEES   
   
  

  
INSERT delete from "HR"."DEPARTMENTS" where ROWID = ‘AAAWXZAAEAAAAZMAAb‘; DEPARTMENTS
   
  

  
BEGIN
   
  

  
----如果需要回退，执行上面的undo_sql即可
  
 

Rollback a transaction using the following PL/SQL statement

  
 

   
  

  
begin 
   
  

  
 for rec in 
   
  

  
 (select undo_sql 
   
  

  
 from flashback_transaction_query 
   
  

  
 where xid=‘10000A0039030000‘) 
   
  

  
 loop 
   
  

  
 if rec.undo_sql is not null then 
   
  

  
 execute immediate substr(rec.undo_sql,1,length(rec.undo_sql)-1); 
   
  

  
 end if; 
   
  

  
end loop; 
   
  

  
commit; 
   
  

  
end; 
   
  

  
/
  
 

From for notes (Wiz)

Flashback transaction Query