Over the past 27 years, Oracle has made a lot of improvement in its core database products. This product is now not only the best database of the world's most reliable and performance, but also components for the full software infrastructure for enterprises. With the emergence of each new version, sometimes the new features and features, sometimes make developers, IT managers, and even experienced DBA, which new features will bring the biggest benefits to them.
With the launch of Oracle Database 10g, DBA will get one of the most deepest new versions that Oracle has always provided. Therefore, for those spent time, how to properly apply new Oracle technology to DBA in their daily work, they will like many provinces and will eventually save new features.
Oracle Database 10 G provides many new tools that help DBA will work more efficient (or more enjoyable), so that they are liberated, do more strategic, creative work - not to talk about them at night and weekends overtime jobs. Oracle Database 10 G is indeed a DBA effective tool.
In the new 20 weeks, I will help you learn more about this powerful new version by giving the Oracle Database 10g I think is the best top 20 new features provided by the database management task. The list contains content from basic features (such as setting default tables for creating users) to advanced features such as new automatic storage management features.
In this series, I will provide a brief and concentrated analysis of these interesting new tools and technologies. Its purpose is to overview features and advantages so that you can apply it to your environment as soon as possible.
You are welcome to make your ideas, comments, and questions about this series. I wish you happy!
First week
Get the movie instead of the picture: Flashback version query
Do not set up, immediately identify all changes to the row
In Oracle9 I Database, we see "Time Machines" expressed in the form of flashback query. 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 10 G is more convenient to efficiently execute the task.
Query changes to table changes
In this example, I used 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 - or even deletes a row and re-insert 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, the DBA will get the current submission value of the Rate column through the following command.
SQL> SELECT *.
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 0002002800000C 61 i 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A 00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A 000B00000029 U 1.1013
01-DEC-03 03.57.55 PM 000A 000C 00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A 000E00000029 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 000a 000d0000000029, the undo_sql value shows the actual statement.
SELECT undo_sql
From Flashback_Transaction_Query
WHERE XID = '000A 000D00000029';
Undo_sql
-------------------------------------------------- ----------------------------
INSERT INTO "Ananda". "Rates" ("currency", "raate") VALUES ('EURO', '1.1013');
In addition to the actual statement, the view also displays the time tag of the submit operation and the SCN and the SCN and time tag and other information at the beginning of the query.
Find changes in a period of time
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 in the afternoon, not all scope changes:
SELECT VERSIONS_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 000A 000C 00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A 000E00000029 u 1.1011
Ultimate analysis
Flashback version query takes short-term volatile value auditing for the use of the use of the use of the use. This advantage makes the DBA can get all the changes in the past period rather than a particular value, as long as the data is provided in the segment, you can use it. Therefore, the biggest available version relies on the undo_retrion parameter.
For more information on flashback version query, see Oracle Database Concepts 10g Release 1 (10.1) Guide
