Oracle Flashback flash back-flash back Query

Flashback is a feature provided by Oracle since 9i. In 9i, we use oracle to query data that has been operated in a table for a certain period of time, this feature is also called Flashback Query. In 10 Gb, Flashback has been greatly enhanced. Using the recycle bin and flash back area features, you can quickly restore and delete a Table (Flashback Table) or restore a Database at a time point (Flashback Database).

I. Flashback Query

Flashback Query uses the multi-version read consistency feature to read the record data before the operation from the UNDO tablespace!

What is multi-version read consistency?

Oracle adopts a very good design. It uses undo data to ensure that writing is not blocked. In short, when writing data for different transactions, the pre-image of the data is written to the undo tablespace. If other transactions query the table data at the same time, you can use the pre-image of the data in the undo tablespace to construct the required complete record set, you do not need to wait for the written transaction to commit or roll back.

Flashback query can be used to construct a query record set in multiple ways. (Note that to use the flashback feature, you must enable automatic revocation of table space management.) the record set selection range can be based on time or scn, you can even query the front image of the records in different transactions in the undo tablespace at the same time. The usage is very similar to the standard query. To use the flashback query to query undo data, the simplest method is to keep up with the as of timestamp (based on time) after the name of the standard query statement) or as of scn (based on scn.

1. Example of As of timestamp:

First, create a simple table and insert some records for testing:

SQL> create table t_fb_test (v_id, va) 2 select 1, 'A' from dual 3 union 4 select 2, 'B' from dual 5 union 6 select 3, 'c' from dual 7 union 8 select 4, 'd from dual 9 union 10 select 5, 'E' from dual 11 union 12 select 6, 'F' from dual; Table created SQL> select * from t_fb_test;   V_ID VA ------------ 1 2 B 3 c 4 d 5 e 6 f 6 rows selected

Delete several records and submit them:

SQL> delete from t_fb_test where v_id <4; SQL> commit;

Now we start to restore the query:

SQL> select * from t_fb_test as of timestamp sysdate-5/1440;   V_ID VA ------------ 1 2 B 3 c 4 d 5 e 6 f   6 rows selected

Haha, the data is retrieved again (if the error: ORA-01466: unable to read data-the table definition has been changed-This shows that your interval estimation is wrong, you haven't created this table for pulling 5 minutes before the operation is too fast .)

By adding the as of timestamp syntax, we can find the pre-record image 5 minutes ago in the undo tablespace. With it, we can easily and quickly restore the record:

SQL> insert into t_fb_test 2 select * from t_fb_test as of timestamp sysdate-3/1440 where v_id <4;   3 rows inserted   SQL> commit;

As of timestamp is indeed very easy to use, but in some cases, we recommend that you use as of scn to execute the flashback query, for example, to restore multiple tables with primary and foreign key constraints, if the as of timestamp method is used, data selection or insertion may fail due to inconsistent time points, the scn method ensures the consistency of the constraints of the record.

2. As of scn:

We use the dbms_flashback.get_system_change_number function to obtain the current scn of oracle, and then execute the data modification operation.

SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 14229608

Delete data:

SQL> delete from t_fb_test where v_id> 4; 2 rows deleted SQL> commit;

Flash back query:

SQL> select * from t_fb_test as of scn 14229608;   V_ID VA ------------ 1 2 B 3 c 4 d 5 e 6 f 6 rows selected

 

Then we can use the insert statement to restore data with the help of the as of scn query results.

In fact, Oracle uses scn internally. Even if you specify as of timestamp, oracle converts it to scn. There is a table between the system time mark and scn, SMON_SCN_TIME in SYS

Every five minutes, the system generates a matching system time mark with scn and stores it in sys. smon_scn_time table, which records the matching records of the last 1440 system time tags and scn. Because this table only maintains the most recent 1440 records, therefore, if you use the as of timestamp method, you can only use flashback data in the last five days (assuming that the system is continuously running without interruption or shutdown or restarting ). Note that the system time mark matches with scn every 5 minutes. For example, scn: 339988,339989 matches 08-05-30 13:52:00 and 2008-13:57:00 respectively, then, when you query the time in the period from 08-05-30 13:52:00 or 08-05-30 13:56:59 through the as of timestamp, oracle will match it with scn: 339988 to the undo tablespace, that is, no matter what time point you specify, the query will return data at the time 13:52:00-05-30.

Of course, you may want to execute select scn, to_char (time_dp, 'yyyy-mm-dd hh24: mi: ss') from sys. smon_scn_time will be more profound.

For details about the conversion between SCN and timestamp/date and the implementation mechanism of SCN, refer to my another blog titled "Summary of Oracle SCN implementation mechanism:

1 2 Next Page