Performing a Flashback Table Operation

In this example, you rewind the hr.employees table and its dependent tables to a previous point in time.

To perform the Flashback Table operation:

  1. Connect Oracle Recovery Manager (RMAN) to the target database as described in "Connecting to the Target Database Using RMAN."
  2. Determine whether the table that you intend to flash back has dependencies on other tables.

    Use the following SQL query to determine the dependencies for the hr.employees:

    SELECT other.owner, other.table_name 
    FROM sys.all_constraints this, sys.all_constraints other
    WHERE this.owner = 'HR'
      AND this.table_name = 'EMPLOYEES'
      AND this.r_owner = other.owner
      AND this.r_constraint_name = other.constraint_name
      AND this.constraint_type='R';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
HR                             EMPLOYEES
HR                             JOBS
HR                             DEPARTMENTS
  3. Ensure that row movement is enabled for the table that you want to flash back and its dependent tables.

    In this example, row movement must be enabled for the tables hr.employees, hr.jobs, and hr.departments using the steps described in "Enabling Row Movement on a Table."

  4. Identify the time, SCN, or restore point to which you want to return the table.

    In this example, we assume that the rows were accidentally inserted 5 minutes ago. Therefore, you must rollback to a timestamp that is 5 minutes before the current time.

    Note:

    If you do not know the time at which the unwanted changes occurred, you can use the Oracle Flashback Version Query to review all recent changes to the target table. Use of this feature is beyond the scope of this documentation.

  5. Verify that enough undo data exists to rewind the table to the specified target.

    Use the following query to determine how long undo data is being retained:

    SELECT NAME, VALUE/60 MINUTES_RETAINED 
    FROM V$PARAMETER
    WHERE NAME = 'undo_retention';

NAME             MINUTES_RETAINED
---------------  ----------------
undo_retention               15
  6. Use the FLASHBACK TABLE statement to perform a flashback operation for the required tables.

    The following SQL statements return the tables hr.employees, hr.jobs, and hr.departments to the specified time:

    FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');

FLASHBACK TABLE hr.jobs TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');

FLASHBACK TABLE hr.departments TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');

See Also: