附上 oracle document 一段文字
Flashback Query
Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).
Flashback query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simply specifying how long undo should be kept in the database. Using flashback query, a user can query the database as it existed this morning, yesterday, or last week. The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
You set the date and time you want to view. Then, any SQL query you execute operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
With the AS OF SQL clause, you can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot.
For example, suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
DML and DDL operations can use table decoration to choose snapshots within subqueries. Operations such as INSERT TABLE AS SELECT and CREATE TABLE AS SELECT can be used with table decoration in the subqueries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.
See Also:
Oracle9i SQL Reference for information on the AS OF clause
Flashback Query Benefits
Application Transparency
Packaged applications, like report generation tools that only do queries, can run in flashback query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because ther is a consistent version of the database as of the flashback query time.
Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is a lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using flashback query, there are no costs for logging that would be incurred by explicit versioning.
Online Operation
Flashback query is an online operation. Concurrent DMLs and queries from other sessions are permitted while an object is being queried inside flashback query.The speed of these operations is unaffected. Moreover, different sessions can flash back to different flashback times or SCNs on the same object concurrently. The speed of the flashback query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.
Easy Manageability
There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.
--------------------------------------------------------------------------------
Notes:
Flashback query does not undo anything. It is only a query mechanism. You can take the output from a flashback query and perform an undo yourself in many circumstances.
Flashback query does not tell you what changed. LogMiner does that.
Flashback query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. You can in theory use it to move a full table back in time but this is very expensive if the table is large since it involves a full table copy.
Flashback query does not work through DDL operations that modify columns, or drop or truncate tables.
LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.
--------------------------------------------------------------------------------
Some Uses of Flashback Query
Self-Service Repair
Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current table.
E-Mail or Voice Mail Applications
You might have deleted mail in the past. Using flashback query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.
Account Balances
You can view account prior account balances as of a certain day in the month.
Packaged Applications
Packaged applications (like report generation tools) can make use of flashback query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.
In addition, flashback query could be used after examination of audit information to see the before-image of the data. In DSS enviornments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
|
