FLASHBACK_TRANSACTION_QUERY View in Oracle 12c returning no rows

Question

I am learning Flashback Transaction Query in Oracle 12c. I executed some DDL and DML statements. At the end, I executed a select statement on the FLASHBACK_TRANSACTION_QUERY view in order to see the UNDO_SQL value, but it returned no rows. I am curious to know why it is giving me no rows. I researched and found that Supplemental Logging has to be enabled first (SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;). This value is YES.

The queries are as below:-

CREATE TABLE TestAgain
(
   Fld1 VARCHAR2(3),
   Fld2 NUMBER(3)
);

INSERT INTO TestAgain values ('XYZ', 1);
INSERT INTO TestAgain VALUES ('PQR', 2);

COMMIT;

DELETE FROM TestAgain;

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = (SELECT DISTINCT(RAWTOHEX(VERSIONS_XID))
FROM TestAgain VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE);

The last query is returning no rows. Please help me to figure out what I am missing here. P.S.: I am just a novice SQL learner.

Answer 1

You have to enable the minimum supplemental log before using Flashback Transaction Query sys> alter database add supplemental log data;

In addition to the minimum supplemental log for Flashback Query, you need to add supplemental log for primary keys and foreign keys in order to trace their changes in Flashback Transaction Query.

SYS> alter database add supplemental log data (primary key) columns; SYS> alter database add supplemental log data (foreign key) columns;

Answer 2

Break your query to below which will give you the pain area where you are having issues. If you try using below sqls before you start the transaction and after starting the transaction.

SQL> select current_scn,systimestamp from v$database;

This will give you the SCN. Use it to get the value of XID

 select descr,versions_xid from TestAgain
 versions between scn pre and post;

Use this value of XID in flashback_transaction_query

 select xid, undo_sql from flashback_transaction_query
 where xid = hextoraw('03000100EE0A0000');