flashback query！

Oracle 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 change number (SCN).

Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed.

[@more@]

Overview of Oracle Flashback Query

Oracle 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 change number (SCN).

Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.

Using Flashback Query, you 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 can query the history of a given row or a transaction. Using undo data stored in the database, you can view all versions of a row and revert to a previous version of that row. Flashback Transaction Query history lets you examine changes to the database at the transaction level.

You can audit the rows of a table and get information about the transactions that changed the rows and the times when it was changed. With the transaction ID, you can do transaction mining through LogMiner to get complete information about the transaction.

See Also:

• Oracle Database Administrator's Guide for more information on the automatic tuning of undo retention and on LogMiner

• "Automatic Undo Retention"

You set the date and time you want to view. Then, any SQL query you run 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:

• "Overview of Oracle Flashback Features" for an overview of all Oracle Flashback features

• Oracle Database SQL Reference for information on the AS OF clause

Flashback Query Benefits

This section lists some of the benefits of using Flashback Query.

• 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 there 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 allowed while an object is 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.

Note:

• 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 undo changes and can be very efficient if you know the rows that need to be moved back in time. You can 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

This section lists some ways to use 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 environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.

See Also:

• Oracle Database Application Developer's Guide - Fundamentals for more information about using Oracle Flashback Query

• Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_FLASHBACK package

• Oracle Database Administrator's Guide for information about undo tablespaces and setting retention period

--============================================

SQL> select sysdate from dual;

SYSDATE
-------------------
2008/08/24 13:08:18

SQL> select * from t;

ID
----------
1
2
3
4
5

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
46210894

SQL> insert into t values(6);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select *from t as of timestamp to_date('2008/08/24 13:08:18','yyyy/mm/dd hh
24:mi:ss') ;

ID
----------
1
2
3
4
5

SQL> select *from t as of timestamp to_date('2008/08/24 13:08:18','yyyy/mm/dd hh
24:mi:ss') where id<3;

ID
----------
1
2

SQL> select *from t as of scn 46210894;

ID
----------
1
2
3
4
5

SQL> select *from t as of scn 46210894 where id<3;

ID
----------
1
2

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1009415/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1009415/