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 level<=10 ;
delete 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發現如下連結:


我目前使用的測試版本：
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才能顯示資訊！