Oracle Undo query table for historical data

The undo table Space is used to hold undo data. When a DML operation is performed, Oracle writes the old data for these operations to the Undo segment. Operations such as consistent read to the database, transaction rollback, and so on.

In other words, in special cases, we can use it to retrieve the data that has been wrongly manipulated. For example:

SELECT T.fid from Ct_bas_province as of TIMESTAMP to_date (' 2014-11-21 22:01 ', ' yyyy-mm-dd hh24:mi ') t WHERE t.fnumber = ' Hainan Province ';

That is, after the name of the table to be queried, add as of timestamp for the specified time to query the value in that time database.

This is much more convenient than recovering the archive or retrieving the data based on the log.

But don't be happy too early. The use of Undo is limited, in 11g

If the size of the undo table space is fixed, that is, it cannot be automatically extended, then in this case the Undo_retention parameter is automatically ignored and does not take effect, and the database adjusts the undo retention period to the optimal value based on the system activity and the undo table space size.

If the Undo table space is automatically extended, the data will attempt to use the Undo_retention parameter, and instead of overwriting the undo data without expiring (just the data that has no expired committed), the Undo table space is automatically expanded if the table space is low. When the Undo table space extension reaches the MAXSIZE value, the database begins to overwrite the undo data without expiration.

The value of undo_retention can be viewed through the following SQL.

--View system-dependent parameter settings select * FROM v$parameter;--note undo_retention This parameter, which represents the Undo time, in seconds. Default 900 seconds, 15 minutes

Oracle Undo query table for historical data