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