| Flashback II |
Hello, everyone. This
time we will check the second item, row log flashback, in the query
features we mentioned last time.
Row log flashback
can query the changed data by row. It means that the logs can be
checked to see how a row has been updated till its current value.
The required condition is that the data is left in the UNDO
tablespace. Let's confirm it right now.
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
The test data is as
follows. Let's confirm the SCN and time as well.
SQL> SELECT A.*,ORA_ROWSCN FROM FLASHBACK_TEST A;
COL1 COL ORA_ROWSCN
---------- --- ----------
1 111 8303822
2 222 8303822
3 333 8303822
4 444 8303822
5 555 8303822
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------------------------------------
05-02-10 17:01:39.131408 +09:00
|
We will
repeat executing UPDATE and COMMIT row COL1. For convenience, we
number the SQL statements.
SQL> --(1)
SQL> UPDATE FLASHBACK_TEST SET COL2='AAA' WHERE COL1=1;
1 row updated.
SQL> commit;
Committed.
SQL> --(2)
SQL> UPDATE FLASHBACK_TEST SET COL2='BBB' WHERE COL1=2;
1 row updated.
SQL> COMMIT;
Committed.
SQL> --(3)
SQL> UPDATE FLASHBACK_TEST SET COL2='CCC' WHERE COL1=3;
1 row updated.
SQL> COMMIT;
Committed.
SQL> --(4)
SQL> UPDATE FLASHBACK_TEST SET COL2='aaa' WHERE COL1=1;
1 row updated.
SQL> COMMIT;
Committed.
SQL> --Final
SQL> SELECT A.*,ORA_ROWSCN FROM FLASHBACK_TEST A;
COL1 COL ORA_ROWSCN
---------- --- ----------
1 aaa 8303924
2 BBB 8303924
3 CCC 8303882
4 444 8303924
5 555 8303924
|
After
confirming that 5 minutes have passed, let's check the row logs of
the past 5 minutes. (INTERVAL 5)
Explanation of the pseudo rows used in SQL statement
Pseudo row Content
VERSIONS_STRTSCN :SCN where the update started
VERSIONS_ENDSCN :SCN where the update ended
(current data if blank)
VERSIONS_STARTIME :the time when the update started
VERSIONS_ENDTIME :the time when the data was updated
(current data if blank)
VERSIONS_XID :id of the update transaction
VERSIONS_OPERATION :Updated data (I:INSERT U:UPDATE D:DELETE)
SQL> select systimestamp from dual;
SYSTIMESTAMP
----------------------------------------------------------------------
05-02-10 17:06:44.146787 +09:00
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 '5' MINUTE
AND
SYSTIMESTAMP;
Start SCN End SCN Xid O COL1 COL
---------- ---------- ---------------- - ---------- ---
8303924 04002B00CD2D0000 U 1 aaa --(4)
8303882 04001900CD2D0000 U 3 CCC --(3)
8303864 04001600CD2D0000 U 2 BBB --(2)
8303839 8303924 04001400CD2D0000 U 1 AAA --(1)
8303839 1 111
8303864 2 222
8303882 3 333
4 444
5 555
9 rows selected.
|
We will
finish the experiment next time.
Takahisa Omichi
|
|
