Oracle 10g : Flashback Table , Query

Print this post

· Flashback Table :

Flashback Table happens in place by rolling back only the changes made to the table or tables and their dependent objects, such as indexes. Note that Flashback Table is different from Flashback Drop: Flashback Table undoes recent transactions to an existing table whereas Flashback Drop recovers a dropped table; Flashback Table uses data in the undo tablespace whereas Flashback Drop uses the recycle bin.
The FLASHBACK TABLE command brings one or more tables back to a point in time before any number of logical corruptions have occurred on the tables. To be able to flashback a table, you must enable row movement for the table; because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for applications that depend on the table’s ROWIDs to remain constant. Before performing the Flashback Table operation, you first enable row movement in the affected tables, as in the following syntax:

SQL> alter table  enable row movement;

Limitations and Restrictions on Flashback Tables :

· Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

· The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

· Flashback Query :

Flashback Query, available in Oracle 9i, has been enhanced to include two new types of queries:

Flashback Versions Query and Flashback Transaction Query.
Flashback Versions Query allows a user or the DBA to see all versions of a table’s row between two times, and with Flashback Transaction Query you can see all transactions that changed a row between two times.

Flashback Versions Query provides an easy way to show all versions of all rows in a table between two SCNs or time stamps, whether the rows were inserted, deleted, or updated. Even if a row was deleted and reinserted several times, all of these changes are available with Flashback Versions Query.

Flashback Transaction Query, in contrast, drills down into the history of table changes based on a transaction ID. Using Flashback Versions Query, you found out which transaction changed the salary information, but you don’t know who made the change. Flashback Transaction Query provides this additional level of detail.

In contrast to referencing the actual table in Flashback Versions Query, Flashback
Transaction Query uses the data dictionary view FLASHBACK_TRANSACTION_QUERY
to retrieve transaction information for all tables involved in a transaction. This view
provides the SQL statements that you can use to undo the changes made by a particular
transaction.

Flashback Query is Not available after restarting the database.

For Flashback Database >> Click Here

For Flashback Drop >> Click Here