Flashback transaction Query

The flashback transaction query is actually an extension of the flashback VERSION Query query. The flashback VERSION Query shows that we can audit all changes to the table within a period of time, but we can only detect problems, there is no good solution for wrong transactions. However, flashback transaction query provides our solution. We can obtain the transaction history and undo_ SQL from flashback_transaction_query, that is, we can audit what a transaction has done or even roll back a committed transaction.

Flashback transaction query needs to use the flashback_transaction_query view. Let's take a look at the view first.
SQL> DESC flashback_transaction_query;
Name type nullable default comments
-----------------------------------------------------------------------
Xid raw (8) y transaction identifier
Start_scn number y transaction start SCN
Start_timestamp date y transaction start Timestamp
Commit_scn number y transaction commit SCN
Commit_timestamp date y transaction commit Timestamp
Logon_user varchar2 (30) y logon user for transaction
Undo_change # Number Y 1-based undo change number
Operation varchar2 (32) y forward operation for this undo
Table_name varchar2 (256) y table name to which this undo applies
Table_owner varchar2 (32) y owner of table to which this undo applies
Row_id varchar2 (19) y rowid to which this undo applies
Undo_ SQL varchar2 (4000) y SQL corresponding to this undo
It is defined:
Select Xid, start_scn, start_timestamp,
Decode (maid, 0, commit_scn, 281474976710655, null, commit_scn)
Commit_scn, commit_timestamp,
Logon_user, undo_change #, operation, table_name, table_owner,
Row_id, undo_ SQL
From SYS. x $ ktuqqry

Well, now we try to roll back a committed transaction.
In fact, you need to obtain data from flashback_transaction_query. The key issue is to obtain the transaction Xid,
SQL> Create Table Test as select * From all_objects;
Table created.

SQL> set time on
11:15:48 SQL> Delete from test where rownum 10 rows deleted.

11:16:10 SQL> select Xid from V $ transaction;
Xid
----------------
040020.ae010000

11:20:09 SQL> commit;
Commit complete.

Of course, in the test, we can obtain the transaction's 04001_ae010000 when the transaction is not committed.
In actual situations, we cannot track every transaction. To obtain the XId of the committed transaction, we must query through flashback version, as shown in
11:30:32 SQL> select versions_xid, versions_operation
11:30:41 2 from test versions between Timestamp
11:30:41 3 to_date ('2017-04-08 11:15:48 ', 'yyyy-mm-dd hh24: MI: ss ')
11:30:41 4 and maxvalue
11:30:41 5 where versions_xid is not null
11:30:41 order by versions_starttime;
Versions_xid v
-----------------
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
040020.ae010000 d
10 rows selected.

We can see that 10 rows are deleted, corresponding to 10 operations. These 10 operations are the same transaction. Now we use this Xid to obtain undo_ SQL.

SQL> select undo_ SQL from flashback_transaction_query
Where Xid = '040020.ae010000 ';
Undo_ SQL
------------------------------------------------------------------------------
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da
Insert into "test". "test" ("owner", "object_name", "subobject_name", "object_id", "da

The delete statement corresponds to 10 insert statements. If we want to roll back this transaction, we can execute these 10 insert statements (the above statements are complete statements, because of display problems, ).

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 the last transaction, we can use falshback table or flashback query to solve the problem. However, if we execute an incorrect transaction, and executed a series of correct transactions. Using the flashback transaction query, we can even roll back the wrong transaction.

This article is transferred from
Http://www.itpub.net/thread-213225-1-1.html