----------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
20:35:57 SQL> alter database add supplemental log data;
Database altered.
20:38:01 SQL> select * from test;
A
----------
2
3
1
20:38:24 SQL> update test set a=a+10;
3 rows updated.
20:38:36 SQL> commit;
Commit complete.
20:39:37 SQL> col versions_endtime for a20
20:40:25 SQL> col versions_starttime for a20
20:40:36 SQL> set pagesize 100
20:41:56 SQL> select versions_startscn,versions_starttime, versions_endtime, versions_operation,versions_xid from test versions between timestamp to_timestamp('2013/06/23 20:38:00','yyyy/mm/dd hh24:mi:ss') and to_timestamp('2013/06/23 20:39:00','yyyy/mm/dd hh24:mi:ss');
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDTIME V VERSIONS_XID
----------------- -------------------- -------------------- - ----------------
1369588 23-JUN-13 08.38.41 PM U 060001003A030000
1369588 23-JUN-13 08.38.41 PM U 060001003A030000
1369588 23-JUN-13 08.38.41 PM U 060001003A030000
23-JUN-13 08.38.41 PM
23-JUN-13 08.38.41 PM
23-JUN-13 08.38.41 PM
6 rows selected.
20:41:57 SQL> col operation for a10
20:43:07 SQL> col undo_sql for a60
20:43:20 SQL> select operation,undo_sql from flashback_transaction_querywhere logon_user='BYS' andundo_sql like '%TEST%';
OPERATION UNDO_SQL
---------- ------------------------------------------------------------
UPDATE update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA
AAcjAKR';
UPDATE update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA
AAcjAAE';
UPDATE update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA
AAcjAAA';
20:44:12 SQL> select operation,undo_sql from flashback_transaction_query where logon_user='BYS' and undo_sql like '%TEST%' andxid=HEXTORAW('060001003A030000');
OPERATION UNDO_SQL
---------- ------------------------------------------------------------
UPDATE update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA
AAcjAKR';
UPDATE update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA
AAcjAAE';
UPDATE update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA
AAcjAAA';
可以使用XID做为条件 ：
20:45:01 SQL> select operation,undo_sql from flashback_transaction_querywhere xid=HEXTORAW('060001003A030000');
OPERATION UNDO_SQL
---------- ------------------------------------------------------------
UPDATE update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA
AAcjAKR';
UPDATE update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA
AAcjAAE';
UPDATE update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA
AAcjAAA';
BEGIN
可以使用XID做为条件 ：
20:45:17 SQL> select operation,undo_sql from flashback_transaction_querywhere xid='060001003A030000';
OPERATION UNDO_SQL
---------- ------------------------------------------------------------
UPDATE update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA