 |
|
Oracle Tips by Burleson |
Oracle 10g Flashback Row History
versions between scn minvalue and maxvalue;
NAME SALARY
---------- ----------
DANIEL 3000
DANIEL 2000
As you can see, the Flashback Row History
feature retrieves all committed occurrences of the row. It provides
you with a way to view and repair historical data. In addition, it
also provides a new way to audit the rows of a table and retrieve
information about the transactions that changed the rows. You can
use the transaction ID obtained from Flashback Row History to
perform transaction mining using LogMiner or Flashback Transaction
History (see next section) to obtain additional information about
the transaction.
The VERSION BETWEEN clause does not change the
query plan. You can use the clause in a SELECT statement against a
view. However, you cannot use the VERSION BETWEEN clause in a view
definition.
The row history data is stored in the undo
tablespace. The undo_retention initialization parameter specifies
how long the database will keep the committed undo information. If
a new transaction needs to use undo space and there is not enough
free space left, any undo information older than the specified undo
retention period will be overwritten. Therefore, you may not be
able to see all the row histories. However, you can set the undo
tablespace option to RETENTION GUARANTEE to retain all row
histories.
To verify the retention value for the
tablespace, you can issue the following statement:
SQL>
select tablespace_name, retention
2 From dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
EXAMPLE NOT APPLY
USERS NOT APPLY
6 rows
selected.
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
