Flashback Transaction Query

Configuration:

• UNDO Tablespace

• UNDO Tablespace

• Database in Archivelog Mode

• Supplemental Logging enabled for the objects

Limitations:

• Table needs to have a PK.

• Does not work if the table has undergone DDL during the specified period.

Operation:

• Transaction Query

Version:

• 10gR1 / 2003

Licensing:

• Requires Enterprise Edition

Summary

Flashback Transaction Query is a complement to Flashback Versions Query. While Versions Query lets you identify the transaction ID (XID) that modified a record, Transaction Query allows you to find out which user executed the change, the table affected, and the SQL text used in the operation. This post explains the necessary configuration (supplemental logging), limitations, and examples of usage.

Oracle Flashback Transaction Query

1) Concept

I consider it a complement to Flashback Versions Query: we first capture the transaction ID (XID) with Versions Query, and then use Flashback Transaction Query to retrieve details about who executed the change and what SQL text was used.

2) Enabling Supplemental Logging

To use Flashback Transaction Query, the database must be configured with supplemental logging. This forces Oracle to write additional information (not strictly required for database operation) into redo logs, and consequently into archive logs.

Supplemental logging for Primary Keys (which is what we need for this feature) requires that the table has a Primary Key. Sup. log can be configured at the database level or for a specific table.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- Example for a single table:
-- ALTER TABLE guob2025.subscriptions ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

3) Example of Flashback Versions Query and it's output

SELECT
    hextoraw(versions_xid) xid,
    TO_CHAR(versions_starttime, 'YYYY-MM-DD HH24:MI:SS') xid_time,
    CASE
        WHEN versions_operation = 'I' THEN 'INSERT'
        WHEN versions_operation = 'U' THEN 'UPDATE'
        WHEN versions_operation = 'D' THEN 'DELETE'
    END AS operation,
    id,
    name
FROM guob2025.subscriptions
VERSIONS BETWEEN
    TIMESTAMP TO_TIMESTAMP('2025-08-20 16:30:00','YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2025-08-20 16:51:00','YYYY-MM-DD HH24:MI:SS')
WHERE id IN (2504, 2505)
ORDER BY 2;
  
XID                  XID_TIME             OPERATION      ID   NAME
-------------------- -------------------- ---------- ------ --------------------
0100080043030000     2025-08-20 16:34:09  INSERT       2504  Adriano Bonacin
07000C0026030000     2025-08-20 16:35:09  UPDATE       2504  Bona
05001A0016030000     2025-08-20 16:46:45  INSERT       2505  Acacio Lima Rocha
02001B0029030000     2025-08-20 16:48:01  UPDATE       2505  Lima Rocha
020014002A030000     2025-08-20 16:50:22  DELETE       2505  Lima Rocha
020014002A030000     2025-08-20 16:50:22  DELETE       2504  Bona

4) Using Flashback Transaction Query

With the XID obtained above, we can now query FLASHBACK_TRANSACTION_QUERY to retrieve the responsible user, the schema, and the SQL statement.

SELECT
    logon_user "user",
    table_owner "owner",
    undo_sql
FROM
    flashback_transaction_query f
WHERE
    xid = hextoraw('02001B0029030000');

Example Output

USER  OWNER     UNDO_SQL
----- --------- -------------------------------------------------------
JC    GUOB2025  update "GUOB2025"."INSCRITOS" set "NAME" = 
                'Lima Rocha' where ROWID = 'AAASJmAAQAAAACFAAA';