8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Flashback Transaction Query in Oracle Database 10g

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.

Flashback Transaction Query
Considerations

Flashback Transaction Query

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');


XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE                               725208     725209
TEST
update "TEST"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN                                725208     725209
TEST

XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------


2 rows selected.

The UNDO_SQL column contains the DML statement that could be used to undo the operation.

Considerations

There are a few things to consider when using flashback query.

Flashback transaction query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.
The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.
Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.
Oracle 11g introduced flashback transaction, which uses flashback transaction query under the hood to simplify backing out a transaction.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.