Oracle 10 Gb Deleted Data Query Methods

Oracle 10 Gb Deleted Data Query methods (1) as of timestamp eg. SQL code select * from t_table as of timestamp (sysdate-1/24) where... note: The table alias (2) as of scn SQL code SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER -------------------- 1859181 -- if you do not know the exact scn, try a smaller SQL statement> select count (*) from t_table as of scn 1800000; SQL> insert into t_table select * from t_table as Scn 1800000; www.2cto.com ------------------------------------------- The following is the reference OF the conversion [to] Time-Based Query (as of timestamp) Application Flashback Query to Query the past data Flashback Query, the most common application is to repair misoperation data. Note: This does not mean that Flashback Query can restore data. Flashback Query itself does not recover any operations or modifications, nor can it tell you what operations or modifications have been made. In fact, the Flashback Query feature is based on standard SELECT extension in actual applications, with this feature, you can query the records in the table at the specified time point, which is equivalent to having the ability to see the past. As for restoration, all the SELECT results are returned, don't you know how to execute insert table select or create table as select?
11.1.1.1 the FLASH_TBL table created before time-based Query (as of timestamp) is used AS an example. DELETE several records and submit the following records: JSSPRE> DELETE FLASH_TBL where id <10; 9 rows deleted. JSSPRE> COMMIT; Commit complete. JSSPRE> SELECT * FROM FLASH_TBL; id vl ---------- -- 10 K 11 L 12 M 13 N 14 O 15 P 16 Q 17 R 18 S 19 T 20 U 11 rows selected. at www.2cto.com, the ID <10 records in the FLASH_TB1 table have been deleted. After a while, the user will find that the deletion operation is incorrect. What should I do if I still need to retrieve the deleted records? Is it restored through backup? If it is in the 8i or earlier version, I am afraid this is the case. After 9i, use the Flashback Query feature, we can easily recover records (note that records can not be recovered under any circumstances. We will discuss some factors limiting the Flashback Query in the following sections. The assumptions here are all under ideal conditions ). Now, we will demonstrate the application OF Flashback Query. First, we will find it. Assume that the current time is about five minutes before the data is deleted, execute the SELECT Query statement, and append the as of clause, for example: JSSPRE> SELECT * FROM FLASH_TBL as of timestamp SYSDATE-5/1440; id vl ---------- -- 1 A 2 B 3 C 4 D 5 E 6 F 7G 8 H 9 I 10 K 11 L 12 M 13 N 14 O 15 P 16 Q 17 R 18 S 19 T 20 U 20 rows selected. JSSPRE> request www.2cto.com SYSDATE-5/1440 is what meaning, 1440 is how to come? First 60 (minute) × 24 = 1440, So calculate the number of minutes a day, SYSDATE is a system function, used to obtain the current system time (in days), SYSDATE-5/1440, the result is the record 5 minutes ago from the current time. In the following example, this method is used when you need to calculate a previous time period. AS you can see, by adding the as of timestamp syntax, the queried data is five minutes ago. Based on this result, you can easily and quickly restore the record:
JSSPRE> insert into FLASH_TBL 2 SELECT * FROM FLASH_TBL as of timestamp SYSDATE-5/1440 3 where id <10; 9 rows created. JSSPRE> COMMIT; Commit complete. nine records are successfully inserted and the current record in the table is queried: JSSPRE> SELECT * FROM FLASH_TBL; id vl ---------- -- 10 K 11 L 12 M 13 N 14 O 15 P 16 Q 17 R 18 S www.2cto.com 19 T 20 U 1 A 2 B 3 C 4 D 5 E 6 F 7G 8 H 9 I 20 rows selected. the data has been successfully restored. AS shown in the above example, the as of timestamp method is very convenient, but in some cases, we recommend that you use the as of scn method to execute the Flashback Query. If you want to restore multiple tables with primary and foreign key constraints, if you use the as of timestamp method, data selection or insertion may fail due to inconsistent time points, the as of scn method ensures that the record processing time is consistent. Author slendersEye