 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 8 -
Miscellaneous Oracle Information
Oracle9i – Flashback
Query
To take advantage of flashback queries, the database must use system
managed undo segments. If flashback query is to be used, the
administrator is tasked with determining how much of the old data
should be kept available. The undo tablespace must be sized to hold
the desired amount of undo data. Oracle documentation provides
calculations that use update frequency and the amount of data being
changed to estimate the required size of the undo tablespace.
The configuration parameter undo_retention, which specifies the amount of time that
Oracle attempts to keep undo data available, plays an important role
in flashback query. Although Oracle documentation recommends
flashback query for applications that want to view data in the past,
it is important to understand that the undo_retention
parameter does not force Oracle to keep the old data in the undo
tablespace. Depending on the available disk storage allocated to the
undo tablespace, the database might not always be able to keep all
of the requested undo data available. Providing active transactions
with undo image space takes precedence over flashback query
requirements. As a result, applications should not be designed to
depend on the availability of historical data retrieved from undo
segments.
The system supplied package dbms_flashback is used to provide flashback query
capabilities. Standard date and time SQL functions can be used to
determine the time in the past the data will be retrieved from. Here
is an example that goes back five minutes:
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE
- (5/(24*60)));
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
