You want to be able to retrieve the inventory of all items at the beginning of the year from the table inventory, and to be able to retrieve the stock price for each symbol in your portfolio at the close of business on any specified day of the year from the table stock_data. Oracle recommends that all users who must use Flashback Archive have unlimited quota on the Flashback Archive tablespace; however, if this is not the case, you must grant sufficient quota on that tablespace to those users. Notes on Flashing Back Dropped TablesThe following notes apply to flashing back dropped tables: Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes and domain indexes. The table must have resided in a locally managed tablespace other than the SYSTEM tablespace. Before you can use flashback features in your application, you or your database administrator must perform the configuration tasks described in these topics: Configuring Your Database for Automatic Undo Management, Configuring Your Database for Oracle Flashback Transaction Query, Configuring Your Database for Flashback Transaction, Enabling Oracle Flashback Operations on Specific LOB Columns. Therefore, if you specify a time that is slightly after a DDL operation (such as a table creation) Oracle Database might use an SCN that is immediately before the DDL operation, causing error ORA-01466. Oracle Database SQL Language Reference for information about the ALTER TABLE statement, Oracle Database SQL Language Reference for information about the TRUNCATE TABLE statement, Oracle Database SQL Language Reference for information about the RENAME statement, Oracle Database SQL Language Reference for information about the DROP TABLE statement, Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_FLASHBACK_ARCHIVE package.

Oracle Database Performance Tuning Guide for information about setting the large pool size. This statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code that shows the logical opposite of the operation: This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change: If you query FLASHBACK_TRANSACTION_QUERY without specifying XID in the WHERE clause, the query scans many unrelated rows, degrading performance. The static data dictionary view *_FLASHBACK_TXN_REPORT provides a detailed report for each backed-out transaction. archive, ensuring SQL-level access to the versions of database objects without For example: Include DEFAULT in the CREATE FLASHBACK ARCHIVE statement when you create a Flashback Archive. These flashback features are primarily for data recovery. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; an Oracle Flashback Query for 8:46 AM is mapped to SCN 1005. To configure your database for Automatic Undo Management (AUM), you or your database administrator must: Create an undo tablespace with enough space to keep the required data for flashback operations. you specify a time for it that is earlier than its creation time, the query returns zero FLASHBACK To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. You can store the results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. The Flashback Table operation is executed in a single transaction, regardless of the number of tables specified in the Flashback list. To allow queries on all tables, grant the FLASHBACK ANY TABLE privilege.

Oracle Database Backup and Recovery User's Guide, Restoring a Table to an Earlier State: Examples, Description of the illustration ''flashback_table.gif''. Use the DBMS_FLASHBACK.ENABLE and DBMS_FLASHBACK.DISABLE procedures around SQL code that you do not control, or when you want to use the same past time for several consecutive queries. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time.

If a table has Flashback Archive enabled, and you try to enable it again with a different Flashback Archive, an error occurs. Flashback Version Query allows index-only access only with IOTs (index-organized tables), but index fast full scan is not allowed. If very large transactions (such as affecting more than 1 million rows) are performed on tracked tables, set the large pool size high enough (at least 1 GB) for Parallel Query not to have to allocate new chunks out of the SGA. It is useful for compliance with record stage policies and audit reports. This chapter explains how to use Oracle Flashback Technology in database applications. Configuring Your Database for Oracle Flashback Technology, Using Oracle Flashback Query (SELECT AS OF), Using Oracle Flashback Transaction Query with Oracle Flashback Version Query, General Guidelines for Oracle Flashback Technology, Performance Guidelines for Oracle Flashback Technology, Multitenant Container Database Restrictions for Oracle Flashback Technology. To allow access to the features in the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK. To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables. FLASHBACK Use Oracle Flashback Query, Oracle Flashback Version Query, and Oracle Flashback Transaction Query locally or remotely. Identifier of the transaction that created the row version. The default value is the release number of the most recent major release. She calls Bob immediately and asks him to correct the error. Oracle Database SQL Language Reference for information about the GRANT statement, For Oracle Flashback Query and Oracle Flashback Version Query.

In this example, a database administrator does this: Now emp and dept have one row each. Comparing current data with the corresponding data at an earlier time. The medical insurance company has its claims in the table Billings, and creates a default Flashback Archive named fla4 that uses up to 100 G of tablespace tbs1, whose data are retained for 10 years: The company enables Flashback Archive for the table Billings, and stores the historical data in the default Flashback Archive: On May 1, 2007, clients were charged the wrong amounts for some diagnoses and tests. Example 19-1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004: Example 19-1 Retrieving a Lost Row with Oracle Flashback Query. To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role. Use this feature to set the internal Oracle Database clock to an earlier time so that you can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored. Therefore, the materialized view logs cannot be flashed back along with the table. (For more information, see Temporal Validity Support.). Refer to row_movement_clause for information on enabling row movement. Use this feature to retrieve metadata and historical data for a specific time interval (for example, to view all the rows of a table that ever existed during a given time interval). The parameters of the TRANSACTION_BACKOUT procedure are: List of transactions to be backed out, identified either by name or by XID, Time hint, if you identify transactions by name.

ARCHIVE Restrictions on Flashing Back TablesThis statement is subject to the following restrictions: 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. Applying packaged applications, such as report generation tools, to past data. A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c. In table b, column b1 has a foreign key constraint on column a1 of table a. To Oracle Database attempts to preserve the original table name. After the FLASHBACK TABLE statement issued above, this list should match the list in the first benchmark. You cannot retrieve past data from a dynamic performance (V$) view. You can have multiple Flashback Archives. Here is a typical use of Oracle Flashback Version Query: You can use VERSIONS_XID with Oracle Flashback Transaction Query to locate this transaction's metadata, including the SQL required to undo the row change and the user responsible for the change. The following two statements return the same rows: If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database retrieves the object that was moved to the recycle bin most recently. Backs out changes to nonconflicting rows of the specified transactions. Suppose that an erroneous transaction deletes empno 111 from table emp: Next, a transaction reinserts empno 111 into the emp table with a new employee name: The database administrator detects the application error and must diagnose the problem. To allow execution of these statements, grant the FLASHBACK ARCHIVE ADMINISTER system privilege: To grant the FLASHBACK ARCHIVE ADMINISTER system privilege, you must be logged on as SYSDBA. Either all of the tables revert to the earlier state or none of them do. Restoring a Table to an Earlier State: ExamplesThe examples below create a new table, employees_test, with row movement enabled, update values within the new table, and issue the FLASHBACK TABLE statement. Oracle Database Reference for more information about *_FLASHBACK_TXN_STATE.

You can view information about Flashback Archive files in static data dictionary views. To use a cursor to store the results of queries, open the cursor before invoking DBMS_FLASHBACK.DISABLE. Fetch from the first cursor to retrieve past data; fetch from the second cursor to retrieve current data. (The Taxes table itself, and history data from transactions less than ten years old, are not purged.).

VERSIONS queries (including CTAS queries) to improve the performance. To create a default Flashback Archive, using either the CREATE For example, you can verify the account balance of a certain day. Table 19-2 Flashback TRANSACTION_BACKOUT Options. To use the DBMS_FLASHBACK package in your PL/SQL code: The database is queried at the specified past time. When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. Table 19-1 Oracle Flashback Version Query Row Data Pseudocolumns. Backs out specified transactions and all dependent transactions in a post-order fashion (that is, children are backed out before parents are backed out).

In terms of row versions, each table has one version of one row. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

They rely on undo data, which are records of the effects of individual transactions. Then he remembers that the employee table has Flashback Archive enabled. List those salaries less than 2500. For example: Oracle Database SQL Language Reference for more information about the CREATE FLASHBACK ARCHIVE statement, Oracle Database SQL Language Reference for more information about the ALTER DATABASE statement.

The second row corresponds to the row in emp that the erroneous transaction deleted. If you are logged on as SYSDBA, you can specify default Flashback Archive in either of these ways: Specify the name of an existing Flashback Archive in the SET DEFAULT clause of the ALTER FLASHBACK ARCHIVE statement. Because undo data for LOB columns can be voluminous, you must define which LOB columns to use with flashback operations.

Make Flashback Archive fla1 the default Flashback Archive: To Flashback Archive fla1, add up to 5 G of tablespace tbs3: To Flashback Archive fla1, add as much of tablespace tbs4 as needed: Change the maximum space that Flashback Archive fla1 can use in tablespace tbs3 to 20 G: Allow Flashback Archive fla1 to use as much of tablespace tbs1 as needed: Change the retention time for Flashback Archive fla1 to two years: Remove tablespace tbs2 from Flashback Archive fla1: Purge all historical data from Flashback Archive fla1: Purge all historical data older than one day from Flashback Archive fla1: Purge all historical data older than SCN 728969 from Flashback Archive fla1: Oracle Database SQL Language Reference for more information about the ALTER FLASHBACK ARCHIVE statement. If you want to retrieve an older version of the table, then do one of these things: Specify the system-generated recycle bin name of the table you want to retrieve. Typically, you use Oracle Flashback Transaction Query with an Oracle Flashback Version Query that provides the transaction IDs for the rows of interest. The database administrator usually calculates the space requirement. Specify a timestamp value corresponding to the point in time to which you want to return the table. You can perform queries on past data in static data dictionary views, such as *_TABLES. For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. You can invoke DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER at any time to get the current System Change Number (SCN). FLASHBACK To disable Flashback Archive for a table, specify NO FLASHBACK ARCHIVE in the ALTER TABLE statement.

You can specify the default Flashback Archive using the CREATE or ALTER FLASHBACK ARCHIVE statements. Oracle Flashback Query lets you retrieve past data directly from the database. You can restore a table while the database is on line, undoing changes to only the specified table. By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Oracle Database Concepts for more information about flashback features, In application development, you can use these flashback features to report historical data or undo erroneous changes. The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables. ARCHIVE statement. In an Oracle Flashback Transaction Query, the xid column is of the type RAW(8). you must either be logged on as SYSDBA or have the Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to more usable names. Enable AUM, as explained in Oracle Database Administrator's Guide. You cannot roll back a FLASHBACK TABLE statement. where user_valid_time refers to the user-specified valid time period, as explained in Temporal Validity Support. FLASHBACK You can store the past data in a temporary table and then use set operators such as MINUS or UNION to contrast or combine the past and current data. The first row corresponds to the version of the row in emp that was reinserted with a new employee name. Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. getting a snapshot-too-old error. You can use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query. You can specify either the original user-specified name of the table or the system-generated name Oracle Database assigned to the object when it was dropped. Create a default Flashback Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data are retained for five years (you must be logged on as SYSDBA): Enable Flashback Archive for the tables inventory and stock_data, and store the historical data in the default Flashback Archive: To retrieve the inventory of all items at the beginning of the year 2007, use this query: To retrieve the stock price for each symbol in your portfolio at the close of business on July 23, 2007, use this query: You want users to be able to generate reports from the table investments, for data stored in the past five years. Oracle Database SecureFiles and Large Objects Developer's Guide to learn about LOB storage and the RETENTION parameter. At first, Bob thinks he cannot return the employee table to its prior state without going to the backups. Create a Flashback Archive with the CREATE You or your database administrator must grant privileges to users, roles, or applications that must use these flashback features. Oracle Database does not revert statistics associated with table to their earlier form. You must invoke DBMS_FLASHBACK.DISABLE before invoking DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER again. Default. Keep the statistics current. To see the records as of May 1, 2007, the company uses this query: An end user recovers from erroneous transactions that were previously committed in the database. The unrealistic duration of 1 minute is used so that you can test this series of examples quickly. Oracle Database keeps undo data for index changes and data changes. Flashback Transaction Backout is not supported in a CDB.

The transactions being rolled back are subject to these restrictions: They cannot have performed DDL operations that changed the logical structure of database tables. Setting UNDO_RETENTION does not guarantee that unexpired undo data is not discarded. Using Oracle Flashback Transaction Query. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted. Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. Scripting on this page enhances content navigation, but does not change the content in any way. FLASHBACK

Oracle Database Administrator's Guide for more information about creating an undo tablespace and enabling AUM, Oracle Database Reference for more information about V$UNDOSTAT. Example 19-2 restores Chung's information to the employees table: Example 19-2 Restoring a Lost Row After Oracle Flashback Query. DATA when creating or altering a Flashback Archive. Buffer cache access is significantly faster than disk access.

First dependent transactions causes an error and appears in *_FLASHBACK_TXN_REPORT. After invoking DBMS_FLASHBACK.DISABLE, open a second cursor.

You can use the RECYCLEBIN synonym instead. Perform queries that return metadata that shows a detailed history of changes to the database, Recover tables or rows to a previous point in time, Automatically track and archive transactional data changes, Roll back a transaction and its dependent transactions while the database remains online. Dropping a Flashback Archive deletes its historical data, but does not drop its tablespaces. That is, it is valid for any time t such that VERSIONS_START* <= t < VERSIONS_END*. Perform regular queries (that is, queries without special flashback-feature syntax such as, Oracle Database PL/SQL Packages and Types Reference, Oracle Data Guard Concepts and Administration.

Oracle Database Backup and Recovery User's Guide.

If ct itself is later backed out, the effects of t1 and t2 are reinstated, and t1 and t2 disappear from *_FLASHBACK_TXN_STATE. Use queries to select small sets of past data using indexes, not to scan entire tables. In the context of Flashback Transaction, transaction 2 can depend on transaction 1 in any of these ways: Transaction 1 changes a row of a table, and later transaction 2 changes the same row. You can enable optimization of data storage for history tables maintained by For example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.