Introduction to Oracle Flashback query

Flashback is a feature that Oracle has been offering since 9i, using Oracle queries in 9i to implement data that has been manipulated for a specified period of time from the rollback segment, to perform data alignment, or to correct error data due to unexpected submissions, This feature is also known as Flashback Query.

First, Flashback Query

As mentioned in the preface, Flashback Query uses the features of multiple-version read consistency to read data from the Undo table space before the operation!

What is multi-version read consistency

Oracle uses a very good design, through the undo data to ensure that the write is not blocked read, simply, different transactions in writing data, the data before the image into the undo table space, so if there are other transactions query the table data, you can through the undo The front image of the data in the tablespace constructs the required complete recordset without the need to wait for write transactions to commit or rollback.

Flashback query sets up a variety of ways to build a set of queries, which can be either based on time or based on the SCN, or even at the same time querying for a forward image that records different transactions in the Undo table space. The usage is very similar to a standard query, and the easiest way to query the undo data in Undo is to flashback the table name of the standard query statement with the as of timestamp (based on time) or as the SCN (based on SCN). The syntax for the as of TIMESTAMP|SCN is not supported since 9ir2.

1, as of timestamp example:

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

The session has changed.

Sql> select Sysdate from dual;

Sysdate

-------------------

2009-10-15 19:04:16

Sql> select * from A;

Id

----------

2

1

3

4

Simulate user error operation, delete data

Sql> Delete from A;

4 rows have been deleted.

Sql> commit;

Submit completed.

Sql> select * from A;

No rows selected

To view the status before deletion:

Suppose the current distance deletes data for about 5 minutes:

Sql> SELECT * from A as of timestamp sysdate-5/1440;

Id

----------

2

1

3

4

Or:

Sql>select * from A as of timestamp to_timestamp (' 2009-10-15 19:04:16 ', ' yyyy-mm-dd hh24:mi:ss ');

Id

----------

2

1

3

4

Restore the previous data with flashback query:

Sql>insert into a SELECT * from a as of timestamp to_timestamp (' 2009-10-15 19:04:16 ', ' yyyy-mm-dd hh24:mi:ss ');

4 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> select * from A;

Id

----------

2

1

3

4

As indicated in the preceding example, as of timestamp is indeed very easy to use, but in some cases we recommend that you execute flashback query using as the SCN, such as when you need to recover multiple tables with mutual primary foreign key constraints, if you use the as of The way of timestamp may result in data selection or insertion failure due to the inconsistency of time point, which can ensure the consistency of the records ' constraints through the SCN method.

2. As of the SCN sample

View SCN:

SELECT Dbms_flashback.get_system_change_number from dual;

SELECT CURRENT_SCN from V$database;

Sql> SELECT CURRENT_SCN from V$database;

Current_scn

-----------

1095782

Delete data:

Sql> Delete from A;

4 rows have been deleted.

Sql> commit;

Submit completed.

To view the status before deletion:

Sql> SELECT * from A as of SCN 1095782;

Id

----------

2

1

3

4

Restore the previous data with flashback query:

sql> INSERT into a select * from a as of SCN 1095782;

4 lines have been created.

Sql> commit;

Submit completed.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/