Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.