|
Flashback II
|
Hi, everyone. This time we want to talk about Flashback, which we discussed some time ago.
Summary
When we want to execute query toward the database at certain time point in the past, it is possible to specify the time or system change number (SCN) by flashback feature to use the data already committed at the relevant time for query. In other words, this might be one of the features used when a database administrator wishes that the previous command had not been executed.
Types
Query features:
(1) Flashback query: Query the data at a certain time point by specifying the time or SCN
(2) Row log flashback: Query the changed data by row
(3) Transaction flashback: Query the changed data by transaction
Update features:
(1) Flashback database: recover the condition of the database to its condition at certain time point in the past
(2) Flashback table: recover a specific table to its condition at certain time point in the past
(3) Flashback drop: recover after executing Drop table
We will delete the data in the test environment and execute flashback. We use Flashback Query instead of DBMS_FLASHBACK package.
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 confirm the query features in order from (1).
SQL> select ora_rowscn,col1,col2 from flashback_test;
ORA_ROWSCN COL1 COL2
---------- ----- -----
2514278 1 111
2514278 2 222
2514278 3 333
2514278 4 444
2514278 5 555
SQL> update flashback_test set col2='000' where col1=3;
One row updated.
SQL> commit;
Committed.
SQL> select col1,col2 from flashback_test;
COL1 COL2
----- -----
1 111
2 222
3 000
4 444
5 555
|
Confirm the time immediately.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
05-01-25 21:54:26.974000 +09:00
|
How about the time point just before the time displayed abover?
SQL> select * from flashback_test
as of timestamp to_timestamp('05-01-25 21:53:00','YY-MM-DD HH24:MI:SS');
COL1 COL2
----- -----
1 111
2 222
3 333
4 444
5 555
|
Here it is! We will continue this experiment next time.
Takahisa Omichi
|
|
