Oracle flashback-Flashback transaction Query

Flashback transaction Query

Flashback transaction query is also implemented using undo information. This function allows you to view all the changes in the execution of a transaction. You need to access the flashback_transaction_query view. The Xid column in this view represents the transaction ID, which can be used to identify all the data changes that occur in a specific transaction.

Example:
SQL> Create Table T (ID int, name varchar2 (30 ));

Table created.

SQL> insert into T values (1, 'test1 ');

1 row created.

SQL> insert into T values (2, 'test2 ');

1 row created.

SQL> insert into T values (3, 'test3 ');

1 row created.

SQL> Update t set name = 'test11' where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> Delete from t where id = 2;

1 row deleted.

SQL> commit;

Commit complete.

Flashback transaction query:

 

SQL> select Xid, operation, undo_ SQL from flashback_transaction_query where table_name = 'T' and table_owner = 'hr' order by start_timestamp DESC;

Xid operation undo_ SQL
----------------------------------------------------------------------------------------------------------
0600160014010000 Delete Insert into "HR". "T" ("ID", "name") values ('2', 'test2 ');
04001800de000000 insert Delete from "HR". "T" where rowid = 'aaam1taaeaaaageaac ';
04001800de000000 insert Delete from "HR". "T" where rowid = 'aaam1taaeaaaageaaa ';
04001800de000000 Update "HR". "T" set "name" = 'test1' where rowid = 'aaam1taaeaaaageaaa ';
04001800de000000 insert Delete from "HR". "T" where rowid = 'aaam1taaeaaaageaab ';

 

View undo_ SQL Based on the XId corresponding to each transaction
SQL> select operation, undo_ SQL from flashback_transaction_query where Xid = hextoraw ('20140901 ');

Operation undo_ SQL
------------------------------------------------------------------------------------------
Delete Insert into "HR". "T" ("ID", "name") values ('2', 'test2 ');

 

As you can see, flashback transaction query is mainly used to audit a transaction and roll back a committed transaction. If the transaction with an error is determined to be the last transaction, we can solve the problem by using the flashback table or flashback query. However, if a wrong transaction is executed and a series of correct transactions are executed, the above method is powerless. You can use flashback transaction query to view or roll back the wrong transaction.