Oracle Flash back Query

First, Introduction

Application to synchronize Oracle updates and delete data, consider using Oracle's flashback query mechanism.

This mechanism first requires Oracle to enable the automatic management of the undo table space Rollback information, and according to the actual situation set the validity period of data preservation, that is, how long to save the data operation?

View Undo table Information Undo: Show parameter undo;

To set the revocation table information:

Alter  Set Undo_managerment=auto; sets to auto to use flashback query alterset undo_retention =  the ; maximum retention time (in seconds) Alter set undo_tablespace=undotbs1;

Second, the use of Flash back query

Reference:

http://www.oracle-developer.net/display.php?id=320

http://www.oracle-developer.net/display.php?id=210

Flashback version query is implemented by extending version between with the WHERE clause.

 is between  to  from  as follows

between TIMESTAMP [lower bound]  and [Upper bound] or  between[lower bound]and[Lower Bound].

Example:

Performed one insert, two updates, one delete on a table

SELECT x, y, z  from   between TIMESTAMP  and MAXVALUE ORDER   by Y;

Results:

X Y Z---------- ------------------------- -----------------------         1 Ten-The-2005  -:xx:57.078Initial Population1 Ten-The-2005  -: on:07.109FirstUpdate         1 Ten-The-2005  -: on:17.125SecondUpdate         1 Ten-The-2005  -: on:17.125SecondUpdate4Rows selected.

Three, the principle of flash back query mechanism implementation

As stated above, Oracle provides a variety of metadata with all version of our data. The metadata is exposed via a number of pseudo-columns so we can use with our flashback version queries. These pseudo-Columns is as follows:
As shown above, Oracle provides a meta-information for each version of our data. These meta-information is displayed through some pseudo-columns, which we can use in the flashback query. These pseudo-columns are as follows: Versions_starttime (start timestamp of version); Version start timestamp VERSIONS_STARTSCN (start SCN of version); The version begins with the system change number (Scn:system changes) versions_endtime (end timestamp of version); Version end time VERSIONS_ENDSCN (end SCN of version); Version end system change number Versions_xid (transaction ID of version); and version versions_operation (DML operation of version). Version operation type (including Add, delete, change) We can now include some of these pseudo-columnsinchOur Flashback version query as follows. Note the SCN metadata is excluded as we are using timestamps forThe examples.
Now we can include these pseudo-columns in the following Flashback query SQL statement, not including the SCN meta information because we used timestamps in this example. SQL>SELECT z2, Versions_starttime3, Versions_endtime4, Versions_xid5, Versions_operation6From FBT VERSIONS between TIMESTAMP MINVALUE and MAXVALUE7ORDER by8Versions_endtime; Z versions_starttime versions_endtime Versions_xid versions_operation-------------------- ------------------------- ------------------------- ---------------- ------------------Initial PopulationTen-aug-2005  -:xx:53.000  Ten-aug-2005  -: on:05.000040026008a010000 ifirst UpdateTen-aug-2005  -: on:05.000  Ten-aug-2005  -: on:14.000040029008a010000 usecond UpdateTen-aug-2005  -: on:14.000  Ten-aug-2005  -: on:26.000040027008a010000 usecond UpdateTen-aug-2005  -: on:26.000040028008a010000 D4rows selected. This explains why we were seeing the second update row twice. If We look at the versions_operation column, we can see that the second appearance of the final update record is actually The delete operation against it (specified by'D'). Without the versions metadata, the Y timestamp column is actually confusing us into thinking we had both versions of the S Ame record at the same Time.
This explains why we saw two lines of update on the above. If we look at the version_operation column, we can see that the second update is actually logged with the operation type D, which is delete. In addition to meta-information, the time stamp of the Y column also confuses us, and the two-time version of the time is the same. The metadata also gives us a indication that the delete operation is the final version of this data. The end timestamp of the version is NULLwhichtells us that there is no superceding record.
Meta-Information also tells us that the delete operation is the last version of this data. This version of the end timestamp is null also tells us that this is a record with no follow-up action. Interestingly,ifWe had not included aSleepBetween creating the FBT table and adding the single record, it would is likely (based on observations) then all Versions_ * Pseudo-columns (except the ENDTIME and ENDSCN) would be NULL forThe insert record.
Interestingly, if we do not hibernate when creating the FBT table and inserting a record time, it may show (according to observation) that all pseudo columns starting with versions_ (except Endtime and ENDSCN) may be null for this insertion.

Oracle Flash back Query