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:
Connect Oracle Recovery Manager (RMAN) to the target database as described in "Connecting to the Target Database Using RMAN."
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
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."
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.
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
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');