Flashback Transaction in Oracle Database
Published by : Obay Salah , November 19, 2024
In both Flashback Table and Flashback versions, Undo Data is used for retrieve objects.
Flashback Transaction retrieves all Undo Data For Transactions, i.e. at the transaction level, regardless of how many transactions are associated with objects.
To query the Flashback Transaction, we use the following View:
FLASHBACK_TRANSACTION_QUERY
DESC FLASHBACK_TRANSACTION_QUERY;
Because the data in this View is very sensitive, this View is protected by the SELECT ANY TRANSACTION PRIVILEGE privilege. Because querying all operations on the database comes with a very large number of information, it is better to focus on the query using conditions.
For example, if I want to review the operations that were performed on a specific table, it is better to specify the condition 'WHERE TABLE_NAME=’TABLE_NAME
Let us now assume that I want to make a query about the operations that occurred on the SALARY table.
SELECT START_TIMESTAMP,UNDO_SQL,TABLE_NAME FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='DEPT';
VERSIONS FLASHBACK can be used first to determine the Transaction Identifier (XID).
SELECT SALARY,VERSIONS_XID AS XID,VERSIONS_STARTSCN,VERSIONS_ENDSCN,VERSIONS_O PERATION FROM SALARY VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE EMP_NO=1;
We get the XID and then use it in the Flashback Transaction Query.
SELECT START_TIMESTAMP,UNDO_SQL,TABLE_NAME FROM FLASHBACK_TRANSACTION_QUERY WHERE XID=HEXTORAW('10050D0000307500');
So we used FLASHBACK_VERSIONS AND FLASHBACK_TRANSACTION.
Generally, FLASHBACK_TRANSACTION is used to display the operations that occurred in the database and this type is also used for Information Undo,
It is also called FLASHBACK_TRANSACTION_QUERY.
Comments
no comment yet!