FLASHBACK_TRANSACTION_QUERY 11G R2. UNDO_SQL为NULL的问题

[20111212]FLASHBACK_TRANSACTION_QUERY 11G R2.txt

1.建立测试表：
create table t1 as select rownum id,'test' from dual connect by leveldelete from t1 where mod(id,2)=0;
commit;

2.查询:
SELECT   versions_starttime, versions_endtime, versions_xid, versions_operation, versions_startscn, versions_endscn, t1.*
    FROM t1
         VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
   WHERE versions_operation IS NOT NULL
ORDER BY ID


VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_XID,VERSIONS_OPERATION,VERSIONS_STARTSCN,VERSIONS_ENDSCN,ID,NAME
2011-12-12 16:28:56.,,080005004C0D0000,D,3583079,,2,test
2011-12-12 16:28:56.,,080005004C0D0000,D,3583079,,4,test
2011-12-12 16:28:56.,,080005004C0D0000,D,3583079,,6,test
2011-12-12 16:28:56.,,080005004C0D0000,D,3583079,,8,test
2011-12-12 16:28:56.,,080005004C0D0000,D,3583079,,10,test


3.XID='080005004C0D0000'

SELECT *   FROM flashback_transaction_query  WHERE xid = HEXTORAW ('080005004C0D0000');

XID,START_SCN,START_TIMESTAMP,COMMIT_SCN,COMMIT_TIMESTAMP,LOGON_USER,UNDO_CHANGE#,OPERATION,TABLE_NAME,TABLE_OWNER,ROW_ID,UNDO_SQL
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,1,UNKNOWN,T1,,,
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,2,UNKNOWN,T1,,,
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,3,UNKNOWN,T1,,,
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,4,UNKNOWN,T1,,,
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,5,UNKNOWN,T1,,,
080005004C0D0000,3583074,2011-12-12 16:28:50,3583079,2011-12-12 16:28:56,SCOTT,6,BEGIN,,,,

undo_sql是NULL，没有信息。OPERATION='UNKNOWN'.

4.google发现如下链接:
https://forums.oracle.com/forums/thread.jspa?threadID=1107387&tstart=1&messageID=4469123

我目前使用的测试版本：
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

必须执行与打开alter database add supplemental log data;

这样FLASHBACK_TRANSACTION_QUERY才能显示信息！