|
Flashback II
|
This time we would like continue the unfinished experiment which we briefly introduced last time.
Environment
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
|
Experiment
We should confirm the test data first.
SQL> select * from flashback_test;
C COL2
- ----------
1 TRN1
2 TRN2
3 TRN3
4 TRN3
|
Then we update the data step by step.
SQL> --Transaction-(4)
SQL> update flashback_test set col2='TRN4' where col1=4;
1 row updated.
SQL> update flashback_test set col2='TRN4' where col1=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> --Transaction-(5)
SQL> update flashback_test set col2='TRN5' where col1=2;
1 row updated.
SQL> update flashback_test set col2='TRN5' where col1=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> --Transaction-(6)
SQL> update flashback_test set col2='TRN6' where col1=4;
1 row updated.
SQL> update flashback_test set col2='TRN6' where col1=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> --Final form
SQL> select * from flashback_test;
C COL2
- ----------
1 TRN6
2 TRN5
3 TRN5
4 TRN6
|
Then we confirm the row log.
SQL> SELECT VERSIONS_STARTSCN "Start SCN", VERSIONS_ENDSCN "End SCN",
VERSIONS_XID "Xid", VERSIONS_OPERATION "Operation", COL1,COL2
FROM FLASHBACK_TEST
VERSIONS BETWEEN TIMESTAMP
SYSTIMESTAMP - INTERVAL '15' MINUTE
AND
SYSTIMESTAMP;
Start SCN End SCN Xid O C COL2
---------- ---------- ---------------- - - ----------
527579 01001800B1020000 U 1 TRN6 (6)
527579 01001800B1020000 U 4 TRN6 (6)
527541 03000D00C7060000 U 3 TRN5 (5)
527541 03000D00C7060000 U 2 TRN5 (5)
527076 527579 01001300B1020000 U 1 TRN4 (4)
527076 527579 01001300B1020000 U 4 TRN4 (4)
527076 1 TRN1
527541 2 TRN2
527541 3 TRN3
527076 4 TRN3
10 rows selected.
|
We want to execute undo command from this point by referring to UNDO_SQL from the XID written above and executing them in order. Please pay attention that applying UNDO_SQL should be just like untangling a tie.
SQL> conn / as sysdba
Connected.
SQL> SELECT UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE XID='01001800B1020000'; -->Specify the XID of (6)
UNDO_SQL
--------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN4
' where ROWID = 'AAAMVJAAFAAAAHVAAA';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN4
' where ROWID = 'AAAMVJAAFAAAAHVAAD';
SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='03000D00C7060000';-->Specify the XID of (5)
UNDO_SQL
--------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN3
' where ROWID = 'AAAMVJAAFAAAAHVAAC';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN2
' where ROWID = 'AAAMVJAAFAAAAHVAAB';
SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='01001300B1020000;-->Specify the XID of (4)
UNDO_SQL
--------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN1
' where ROWID = 'AAAMVJAAFAAAAHVAAA';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN3
' where ROWID = 'AAAMVJAAFAAAAHVAAD';
|
And then we execute the UNDO_SQL obtained above. (SQL is simple, so we neglect it here.)
SQL> select * from flashback_test;
C COL2
- ----------
1 TRN1
2 TRN2
3 TRN3
4 TRN3
|
It is back! And that is all for today.
Takahisa Omichi
|
|
