Oracle flashback-based flashback Query

In Oracle 10 Gb, the flash back family is divided into the following members:
Flashback Database
Flashback drop
Flashback table
Flashback query (flashback query, flashback version query, and flashback transaction query)
The following describes flashback 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.
I. Flashback Query
As mentioned in the preface, 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. The record set range can be time-based or SCN-based. It can even be used to query the front image of 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. As of Timestamp | the SCN syntax is supported only after 9ir2.

This function has nothing to do with flashback on/off and recyclebin ON/OFF.
1. Example of as of Timestamp:
SQL> select * From test1;
ID name
--------------------
3764577 1
SQL> select to_char (sysdate, 'yy-mm-dd hh24: MI: ss') time from dual;
Time
-----------------
12-01-13 16:59:29

Simulate user misoperations and delete data
SQL> ALTER TABLE test1 enable row movement; -- to change the table to allow row movement.
SQL> Delete from test1;
SQL> commit;
SQL> select * From test1;
No rows selected

View the status before deletion:
Assume that the data has been deleted for about 5 minutes:
SQL> select * From test1 as of Timestamp sysdate-5/1440;
ID name
--------------------
3764577 1

Or:
SQL> select * From test1 as of Timestamp to_timestamp ('12-01-13 16:59:29 ', 'yy-MM-DD hh24: MI: ss ');
ID name
--------------------
3764577 1
Use flashback to restore the previous data:
SQL> insert into test1 select * From test1 as of Timestamp to_timestamp ('12-01-13 16:59:29 ', 'yy-MM-DD hh24: MI: ss ');
1 row created.
SQL> commit;
SQL> select * From test1;
ID name
--------------------
3764577 1
As shown in the preceding example, the as of timestamp is indeed very easy to use. However, in some cases, we recommend that you use the as of SCN method 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 example
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
-----------
3765554

Delete data:
SQL> Delete from test1;
1 row deleted.
SQL> commit;
View the status before deletion:
SQL> select * From test1 as of SCN 3765554;
ID name
--------------------
3764577 1
Use flashback to restore the previous data:
SQL> insert into test1 select * From test1 as of SCN 3765554;
1 row created.
SQL> commit;
Commit complete.
SQL> select * From test1;
ID name
--------------------
3764577 1

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 the SCN every 5 minutes. For example, SCN: 3764577,3765348 Match 12-01-12 13:52:00 and 12-01-12 13:57:00 respectively, then, when you query the time between 12-01-12 13:52:00 or 12-01-12 13:57:00 through the as of Timestamp, Oracle will match it with SCN: 3764577 to the Undo tablespace, that is, during this time, no matter what time point you specify, the query will return data at 12-01-12 13:52:00.
View the ing between SCN and timestamp:
Select SCN, to_char (time_dp, 'yyyy-mm-dd hh24: MI: ss') from SYS. smon_scn_time;

Flashback VERSION Query
Compared with the flashback query, you can only view the object status at a certain point. The flashback VERSION Query introduced by Oracle 10 Gb shows how the record changes over a certain period of time. Based on this history, the DBA can quickly determine when an error occurs in the data and then restore it to the previous state.
First look at a pseudo column ora_rowscn. the so-called pseudo column is a false data column that does not exist. Although it is not specified when you create a table, some internal fields added to Oracle for maintenance are, these fields can be used as common files.
The most familiar pseudo-column is rowid, which is equivalent to a pointer pointing to the position of the record on the disk. Ora_rowscn is newly added to Oracle 10 Gb. It is regarded as the SCN of the last modified record. The flashback version query uses this pseudo column to track the change history of a record.
For example:
SQL> select * from test; no data

SQL> insert into test values (1, 'A ');
1 row created.
SQL> commit;
SQL> alter system checkpoint;
SQL> insert into test values (2, 'B ');
1 row created.
SQL> commit;

SQL> select ora_rowscn, ID, name from test;
Ora_rowscn ID name
----------------------------------------------
3837792 1
3837792 2 B

Get more historical information
SQL> select versions_xid, versions_startscn, versions_endscn,
2 decode (versions_operation, 'I', 'insert', 'U', 'update', 'D', 'delete', 'original') "operation ",
3 ID, name from test versions between SCN minvalue and maxvalue;

The above information is the change to the "version" of the test table. After obtaining the SCN information, you can find the Undo SQL statement in the flashback_transaction_query view, you can know what the table has done,

And how to cancel the operation.

Select dbms_flashback.get_system_change_number from dual to 3838469

Use the following SQL to get the specific operation:

Select Xid, commit_scn, commit_timestamp, operation, undo_ SQL
From flashback_transaction_query Q where Q. Xid in
(Select versions_xid from test versions between SCN 3837782 and 3838469 );

Below we will talk about the pseudo columns. The flashback VERSION Query Technology actually has many pseudo columns, but ora_rowscn is the most important. It records the SCN of the last modification. Note that the modification is submitted. If not submitted, this pseudo column will not change.
Ora_rowscn is at the data block level by default, that is, all records in a data block are one ora_rowscn, and any record in the data block is modified, the ora_rowscn of all records in this database block will change at the same time. The query result of the preceding example is used to prove the problem.
However, we can use the keyword rowdependencies when creating a table to change this default behavior. With this keyword, each record has its own ora_rowscn.
Example:
SQL> Create Table test1 (ID int, seq INT) rowdependencies;
Table created.
SQL> insert into test1 values (dbms_flashback.get_system_change_number, 1 );
1 row created.
SQL> insert into test1 values (dbms_flashback.get_system_change_number, 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test1 values (dbms_flashback.get_system_change_number, 3 );
1 row created.
SQL> commit;
Commit complete.
SQL> select ora_rowscn, ID, seq from test1;
Ora_rowscn ID seq
------------------------------
3769182 3769179 1
3769182 3769180 2
3769186 3769184 3

It indicates the SCN of the last modification. If it is not submitted, it will not change.

Flashback transaction Query
Flashback transaction query is also implemented using undo information. This function allows you to view all the changes in the execution of a transaction. You need to access the flashback_transaction_query view. The Xid column in this view represents the transaction ID, which can be used to identify all the data changes that occur in a specific transaction.
Example:
SQL> truncate table test1;
Table truncated.
SQL> select current_scn from V $ database;
Current_scn
-----------
3838895
SQL> insert into test1 values (dbms_flashback.get_system_change_number, 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from V $ database;
Current_scn
-----------
3838952

View the view. Each transaction corresponds to the same Xid.
SQL> select Xid, operation, commit_scn, undo_ SQL from flashback_transaction_query where Xid in (
2 select versions_xid from test1 versions between SCN minvalue and maxvalue );
Or
Select Xid, commit_scn, commit_timestamp, operation, undo_ SQL
From flashback_transaction_query Q where Q. Xid in
(Select versions_xid from test1 versions between SCN 3838895 and 3838952 );

After the database is restarted, as long as the corresponding undo information (the Undo information corresponds to the file in the Undo tablespace) is still available, it can still be queried.