First week: flashback query
Get the movie instead of the picture: Flashback version query
No settings, immediately identify all changes to the row in Oracle9i Database, we see that it launches "time machine" represented by flashback query form. This feature allows DBA to see a column value of a specific time, as long as the data block is provided before the data block is provided. However, the flashback query only provides a fixed snapshot of a certain time data, rather than the running status of the data between two points in time. Some applications, such as applications involving foreign currency management, may need to understand changes in value data during a period of time, not just the value of two point points. Due to the flashback version query feature, Oracle Database 10g can make it more easily and efficiently. Introduction to the table changes in this example, I use a bank foreign currency management application. Its database contains a table named Rates that records the exchange rate of a specific time. SQL> DESC RATES
Name NULL? TYPE
------------------------ ------------
Currency varchar2 (4)
Rate Number (15, 10)
This table shows the exchange rate of US $ with various other currencies, displayed in the Currency column. In the financial services industry, the exchange rate is not only updated at the time of change, but also recorded in history. The reason for this way is that bank transactions may take effect in the "past time" to adapt to time due to remittance. For example, for a transaction that occurs in 10:12 in the morning, 9:12 in the morning, its effective exchange rate is the exchange rate of 9:12 am, not the current exchange rate. Until now, the only choice is to create an exchange rate history table to store the exchange rate change, then query whether the table provides a history. Another option is to record the start and end time of a particular exchange rate applicability in the Rates table itself. When a change occurs, the End_Time column in the existing row is updated to sysdate and inserted a new row with a new exchange rate, and its end_time is NULL. However, in Oracle Database 10g, the flashback version query feature does not require maintenance history tables or storage start and end time. With this feature, you don't have to make additional settings, you can get a value of a row in the past specific time. For example, assuming that the DBA updates the exchange rate several times during normal business - even deleting a row and re-inserting the line: INSERT INTO RATES VALUES ('EURO', 1.1012);
COMMIT;
Update Rates set Rate = 1.1014;
COMMIT;
Update rates set rat = 1.1013;
COMMIT;
DELETE RATES;
COMMIT;
INSERT INTO RATES VALUES ('EURO', 1.1016);
COMMIT;
Update rates set rat = 1.1011;
COMMIT;
After this series of operations, DBA will get the current submission value of the Rate column through the following command; SQL> Select * from Rates;
Curr Rate
---- ------------
EURO 1.1011
This output shows the current value of the Rate, and does not display all changes that have occurred from the first time. At this time, you can use the flashback query, you can find the value of a given point in time; but we are more interested in building the audit clues to build changes - some are similar to the change of changes, not just shooting a series of snapshots at a specific point. . The following query shows the changes made to the table: SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION, RATE
From Rates Versions Between TimeStamp Minvalue and MaxValue
Order by Versions_StartTime
/
Versions_StartTime Versions_endtime Versions_xid V Rate
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- - ----------
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 i 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 u 1.1013
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 u 1.1011
Note that all changes made to the row are shown here, even if the row is deleted and re-inserted. The Version_Operation column shows what operations do to do this (INSERT / UPDATE / DELETE). These tasks do not require historical tables or additional columns. In the above query, column Versions_StartTime, Versions_endtime, Versions_XID, Versions_Operation is a pseudo column, similar to Rownum, Level, and other familiar pseudo columns. Other Pseudo Columns - such as Versions_Startscn and Versions_endscn - show the system change number of this moment. Column Versions_XID displays the transaction identifier that changes the row. More details on this transaction can be found in view flashback_transaction_query, where column XID shows the transaction ID. For example, using the above Versions_XID value 000A000D00000029, the undo_sql value shows the actual statement. SELECT undo_sql
From Flashback_Transaction_Query
WHERE XID = '000A000D00000029';
Undo_sql
-------------------------------------------------- ----------------------------
INSERT INTO "Ananda". "currency", "rate") VALUES ('EURO', '1.1013'); In addition to the actual statement, the view also displays the time tag and SCN of the submission operation, the query start SCN and time tags and other information. Find a period of changes in a period now, let us see how to effectively use this information. Suppose we need to find the value of the Rate column at 3:57 pm. We can do: SELECT RATE, VERSIONS_STARTTIME, VERSIONS_ENDTIME
From Rates Versions
Between TimeStamp
TO_DATE ('12 / 1/2003 15:57:54 ", 'mm / dd / yyyy hh24: mi: ss')
And to_date ('12 / 1/2003 16:57:55 ", 'mm / dd / yyyy hh24: mi: ss')
/
Rate Versions_StartTime Versions_ENDTIME
---------- -------------------------------------- ----
1.1011
This query is similar to flashback query. In the above example, the start and end time is empty, indicating that the exchange rate is not changed in this period, but includes a period of time. You can also use SCN to find past version values. SCN numbers can be obtained from the pseudo columnsions_startscn and versions_endscn. The following is an example: Select Rate, Versions_StartTime, Versions_ENDTIME
From Rates Versions
BetWeen SCN 1000 and 1001
/
Use keyword Minvalue and maxValue to display all changes provided in the restore segment. You can even provide a specific date or SCN value as one endpoint of the range, and the other endpoint is text maxValue or minValue. For example, the following query provides changes only from 3:57:52 pm, not all modifications: select version_starttime, versions_endtime, versions_xid,
Versions_Operation, Rate
From Rates Versions Between TimeStamp
TO_DATE ('12/11 / 2003 15:57:52 ',' mm / dd / yyyyh24: mi: ss')
And maxValue
Order by Versions_StartTime
/
Versions_StartTime Versions_endtime Versions_xid V Rate
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- - ----------
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 u 1.1011
