the value of undo_sql in flashback_transaction_query view is always null

Question

so I have a table defined as follow:

`create table test.test as (select * from dual);

and ran the following update statements:

update test.test set dummy = '1' where dummy = 'X';
commit;

I want to find the undo_sql for the transaction above, so I ran the following statement to find the transaction id

SQL> select dummy, versions_xid, versions_startscn, versions_endscn from test.test
versions between scn minvalue and maxvalue ;

D VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN
- ---------------- ----------------- ---------------
1 0A000500B00A0000           2802291
X                                            2802291

and ran the following statement to find the undo_sql statement

SQL> select undo_sql from flashback_transaction_query 
where xid = (select  versions_xid from test.test versions 
between scn minvalue and maxvalue where versions_xid is not null);

UNDO_SQL
--------------------------------------------------------------------------------

as you can see, I got nothing, if I run the following statement, I also get nothing.

SQL> select undo_sql from flashback_transaction_query where undo_sql is not null;

no row selected

looks like undo_sql is always null;

Is there anything I didn't configure ? Here are the values from v$system_parameter

SQL> select name, value from v$system_parameter where name like '%undo%';

NAME                 VALUE
----------------     ----------------
undo_management      AUTO
undo_tablespace      UNDOTBS1
undo_retention       18000

score 3 · Accepted Answer · answered Jun 15 '13 at 04:07

As far as I remember, you should have supplemental logging be enabled so that database can collect the data needed to reconstruct the undo statements used for Flashback Transaction Query:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For more information on enabling supplemental logging, read "Configuring Your Database for Oracle Flashback Transaction Query" section in Advanced Application Developer's Guide and "Supplemental Logging" section in Database Utilities (LogMiner is another utility which depends on supplemental logging).

the value of undo_sql in flashback_transaction_query view is always null

1 Answers1