|
Flashback II
|
Hi, everyone. We will continue the experiment not finished 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
Please refer to the previous article for the tests we did last time.
SQL> select * from flashback_test
as of timestamp systimestamp - interval '10' minute;
No record is selected.
|
Maybe we went back too far. Let's try the other way by specifying SCN.
SQL> select ora_rowscn,col1,col2 from flashback_test
as of scn 2514278
ORA_ROWSCN COL1 COL2
---------- ----- -----
2514278 1 111
2514278 2 222
2514278 3 333
2514278 4 444
2514278 5 555
|
We can also copy the data at a certain time point into a table as follows.
SQL> create table flashback_test_bk as select * from flashback_test where col1=0;
Table created.
SQL> insert into flashback_test_bk select col1,col2 from flashback_test
as of scn 2514278;
5 rows created.
SQL> select * from flashback_test_bk;
COL1 COL2
----- -----
1 111
2 222
3 333
4 444
5 555
SQL> commit;
Committed.
|
However, when the table structure is changed, the data query will become invalid.
SQL> truncate table flashback_test;
Table is truncated.
SQL> select col1,col2 from flashback_test as of scn 2514278;
select col1,col2 from flashback_test as of scn 2514278
*
Error occurs at row 1:
ORA-01466: unable to read data - table definition has changed
|
That is all for today.
Takahisa Omichi
|
|
