Original: http://www.itpub.net/showthread.php? Threadid = 116297 & Pagenumber =
In 9i, if the undo tablespace is used, Oracle automatically manages the rollback segment, and you can keep such a long time in the undo tablespace in the undo tablespace, so you can avoid the Snapshot Too Old error, and you can get a certain point in time via Flashback. Previous data, but here you want to emphasize: 1: Flashback function does not support DDL statement, if the data that has been truncate is not found 2: It looks like this Oracle will be generated every 5 minutes. The SCN corresponds to a TIME to do a record, which usually only records the SCN, but every 5 minutes will record SCN AND TIME. When using Time to do Flashback, it is possible to generate a deviation, please pay attention to the experiment 4! ! ! ! Experiment 1: Using Flashback function SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from Dual; get_system_change_number ---------------------- 36501397sql> Insert Into TF VALUES (3); 1 row created.SQL> commit; Commit complete.SQL> exec dbms_flashback.enable_at_system_change_number (36501397); PL / SQL procedure successfully completed.SQL> select * from tf; A ---------- 12 experiment 2: DDL is then flashback does not support the above steps SQL> truncate table tf; Table truncated.SQL> exec dbms_flashback.enable_at_system_change_number (36501397); PL / SQL procedure successfully completed.SQL> select * from tf; select * from tf * ERROR at line 1 : ORA-01466: Unable to read data - Table Definition Has Changedsql> Exec dbms_flashback.disable; Experiment 3: We create a table, immediately look at the effect, all the process is within 5 minutes, during the time, we will Unable to query data! SQL> drop table tf; Table dropped.SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 36509390SQL> create table tf (a number); Table created.SQL> insert into tf values (1); 1 row created.SQL> commit; Commit complete.SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------- ----------- 36509490sql> Insert Into TF VALUES (2); 1 Row Created.SQL> Commit; Commit Complete.SQL>
exec dbms_flashback.enable_at_system_change_number (36509390); PL / SQL procedure successfully completed.SQL> select * from tf; select * from tf * ERROR at line 1: ORA-01466: unable to read data - table definition has changedSQL> exec dbms_flashback.disable ; PL / SQL procedure successfully completed.SQL> exec dbms_flashback.enable_at_system_change_number (36509490); PL / SQL procedure successfully completed.SQL> select * from tf; select * from tf * ERROR at line 1: ORA-01466: unable to read data - Table Definition Has ChangeDSQL> EXEC DBMS_FLASHBACK.DISABLE; PL / SQL Procedure SuccessFully Completed. Experiment 4: Time to do, we look closely about the following time and data relationship! SQL> SELECT TO_CHAR (Sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual; to_char (sysdate, 'yy ------------------ 2003- 04-26 17:09:04 Time Time1Sql> Create Table Test1 (a Number); Table Created.sql> Select To_Char (Sysdate, 'YYYY-MM-DD HH24: MI: SS') from Dual; To_Char (Sysdate, 'yy ------------------- 2003-04-26 17:09:20 Time Time2Sql> Insert Into Test1 Values after Creating a table 1); 1 row created.sql> commit; commit complete.sql> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; to_char (sysdate,' yy ------- ------------ 2003-04-26 17:09:43 1 record Time3SQL> Insert Into Test1 Values (2); 1 Row Created.sql> CommIT CommT completion.sql> EXEC DBMS_LOCK.SLEP (300); Sleep 5 minutes PL / SQL Procedure SuccessFully Completed.SQL> SELECT TO_CHAR (Sysdate, 'YYYY-MM-DD HH24: MI: SS') from Dual; To_Char (sysdate , 'Yy ------------------- 2003-04-26 17:15:55 After sleeping, insert the data Time4SQL> Insert Into Test1 Values (3); 1 Row Created .Sql> commit; commit complete.sql> EXEC dbms_flashback.enable_at_time (to_date ('2003-04-26 17:09:04 ",' YYYY-MM-DD HH24: MI: SS ') );
