Oracle Database 10g: Best New Features (first week: Flash back query)

Oracle
First week: Flash back query

Get a movie instead of a picture: Flash back version query



No setting required to immediately recognize all changes to a row

In the Oracle9i Database, we saw that it introduced a "time Machine" in the form of a flashback query. This feature allows the DBA to see the column value at a specific time, provided that the previously mirrored copy of the data block is available in the restore segment. However, a flash-back query provides only a fixed snapshot of the data at a given moment, rather than a running state of the data changed between the two points in time. Some applications, such as those involving foreign currency management, may need to understand the changes in numeric data over a period of time, not just the two-point value. Oracle Database 10g can perform this task more easily and efficiently due to the feature of the Flash back version query.

Query changes to a table


In this example, I used a bank foreign currency management application. Its database contains a table called RATES that records the exchange rate for a particular time.

sql> desc ratesname Null? Type-------------------------------------CURRENCY VARCHAR2 (4) RATE number (15,10)

The table shows the exchange rates for US $ with various other currencies, shown in the CURRENCY column. In the financial services industry, exchange rates are not only updated at the time of change, but are recorded in history. The reason for this is that bank transactions may take effect in "past time" to accommodate the time spent on remittances. For example, for a transaction that takes place at 10:12 but takes effect at 9:12, the effective exchange rate is the 9:12 exchange rate, not the current exchange rate.



Until now, the only option is to create a currency history table to store changes in exchange rates, and then query whether the table provides history. Another option is to record the start and end times of a specific 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 a new line with the new exchange rate is inserted, and its end_time is NULL.



However, in Oracle Database 10g, The flash-back version query feature does not need to maintain the history table or save start and end times. With this feature, you can get the value of a row at a specific time in the past without having to make additional settings.



For example, suppose the DBA updated the exchange rate several times during the normal business process-even deleting a row and inserting the row again:

INSERT into rates values (' EURO ', 1.1012); commit;update rates Set rate = 1.1014;commit;update rates Set rate = 1.1013;commi T;delete Rates;commit;insert into rates values (' EURO ', 1.1016); commit;update rates Set rate = 1.1011;commit;

After this series of actions, the DBA will obtain the current commit value of the RATE column with the following command

Sql> SELECT * from rates; CURR RATE--------------EURO 1.1011

This output displays the current value of RATE and does not show any changes that have occurred since the first time the row was created. Using a flashback query, you can find the value at a given point in time, but we are more interested in building the audit trail for changes-some similar to recording changes through a camcorder, rather than just taking a series of snapshots at a particular point.



The following query shows the changes you made to the table:

Select Versions_starttime, Versions_endtime, Versions_xid, versions_operation, rate from rates versions Timestamp MinValue and Maxvalueorder 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.101201-dec-03 03.57.30 pm 01-dec-03 03.57.39 pm 000a000a00000029 U 1.101401-dec-03 03.57. PM 01-dec-03 03.57.55 pm 000a000b00000029 U 1.101301-dec-03 03.57.55 pm 000a000c00000029 D 1.101301-dec-03 03.58.07 pm 01-dec-03 03.58.17 pm 000a000d00000029 I 1.101601-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 reinserted. The Version_operation column shows what action was performed on the row (Insert/update/delete). The work does not require a history table or additional columns.



In the above query, column versions_starttime, Versions_endtime, Versions_xid, versions_operation are pseudo columns, similar to other familiar pseudo columns such as rownum, level, and so on. Other pseudo columns-such as VERSIONS_STARTSCN and versions_endscn--show the system change number for that moment. The column VERSIONS_XID shows the transaction identifier that changed the row. More detailed information about the transaction can be found in the view Flashback_transaction_query, where the column XID displays the transaction ID. For example, the actual statement is displayed using the above Versions_xid value 000a000d00000029,undo_sql value.

SELECT undo_sqlfrom flashback_transaction_querywhere XID = ' 000a000d00000029 '; Undo_sql----------------------------------------------------------------------------INSERT INTO "Ananda". RATES "(" CURRENCY "," RATE ") VALUES (' EURO ', ' 1.1013 ');

In addition to the actual statements, the view also displays the time stamp and SCN for the submit operation, the SCN and time markers at the start of the query, and other information.



Identify changes in a period of time



Now, let's see how to use this information effectively. Suppose we need to find out the value of the RATE column 3:57:54. We can execute:

Select Rate, Versions_starttime, Versions_endtimefrom rates versionsbetween 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 a flash back query. In the above example, the start and end times are empty, indicating that the exchange rate has not changed in that time period, but rather that it contains a time period. You can also use the SCN to find past version values. You can obtain the SCN number from the pseudo columns VERSIONS_STARTSCN and VERSIONS_ENDSCN. Here's an example:

Select Rate, Versions_starttime, Versions_endtimefrom rates versionsbetween SCN 1000 and 1001/

Using keywords MinValue and MAXVALUE, you can display all the changes that are available in the restore segment. You can even provide a specific date or SCN value as an endpoint of the range, and the other endpoint is literal MAXVALUE or minvalue. For example, the following query provides those changes that start only from 3:57:52 instead of the full range of changes:

Select Versions_starttime, Versions_endtime, Versions_xid, versions_operation, rate from rates versions Timestamp to_date (' 12/11/2003 15:57:52 ', ' mm/dd/yyyy hh24:mi:ss ') and Maxvalueorder by Versions_starttime/versions_ StartTime versions_endtime versions_xid V RATE-------------------------------------------------------------------- ---01-dec-03 03.57.55 pm 000a000c00000029 D 1.101301-dec-03 03.58.07 pm 01-dec-03 03.58.17 pm 000a000d00000029 I 1.101601- DEC-03 03.58.17 PM 000a000e00000029 U 1.1011

The final analysis



The flash-back version query takes a short term variable value audit of the copy table changes with the. This advantage allows DBAs to get all of the changes in the past time period instead of a specific value, as long as the data in the restore segment is available. Therefore, the largest available version depends on the undo_retention parameter.

For more information about the Flash-back version query, see the related section of the Oracle Database concepts 10g Release 1 (10.1) Guide.