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才能显示信息！