|
Flashback II
|
Hi, let's finish the experiment half-done last week.
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
Let's check the data by flashback query with the SCN shown above.
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303924;
COL1 COL
---------- ---
1 aaa
2 BBB
3 CCC
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303882;
COL1 COL
---------- ---
1 AAA
2 BBB
3 CCC
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303864;
COL1 COL
---------- ---
1 AAA
2 BBB
3 333
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303839;
COL1 COL
---------- ---
1 AAA
2 222
3 333
4 444
5 555
|
Access to SYSDBA, and specify XID to check the UNDO_SQL row of FLASHBACK_TRANSACTION_QUERY. Let's specify the XID at the UPDATE SQL statement, which is marked as (4) in the experiment explanation last week.
SQL> CONN / AS SYSDBA
SQL> SELECT TABLE_NAME ,OPERATION,UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='04002B00CD2D0000'; ---->Specify XID
TABLE_NAME OPERATION UNDO_SQL
---------- --------------------------- --------------------------------
FLASHBACK_TEST UPDATE update "ORAORA"."FLASHBACK_TEST"
set "COL2" = 'AAA' where ROWID =
'AAANkuAAFAAAAHYAAA';
SQL> select * from "ORAORA"."FLASHBACK_TEST" where ROWID = 'AAANkuAAFAAAAHYAAA';
COL1 COL
---------- ---
1 aaa
UNDO_SQL is created.
|
Conclusion
With row log flashback, it is possble to check how the current data became its current status. The keyword is VERSIONS BETWEEN. This is all for today.
Takahisa Omichi
|
|
