Oracle Flashback Table

Through flashback table we can rewind table in past stage.

Flashback table uses information in the undo tablespace rather than restored backups.

When flashback table operation occurs, rows are deleted and reinserted. The rest of database remain available which the flashback of the table is being performed.

Prerequisites for flashback table
1. Must have FLASHBACK ANY TABLE or FLAHSBACK object privileges

2. Must have SELECT, INSERT, UPDATE , DELETE and ALTER privilege on the table

3. Must have SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY system privileges.

Following objects are eligible for flashback table option
1. The object must not be included the following categories: 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

2. The structure of the table must not have been changed between the current time and the target flash back time.
The following DDL operations change the structure of a table: 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).

3. Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.

4. The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.

Example:

18:30:32 SQL> delete scott.emp;
13 rows deleted.
18:30:38 SQL> commit;
Commit complete.

After commit user found his made delete operation on wrong table now he wants to back all deleted records.

The perform a flashback of SCOTT.EMP

1. obtain SCN information about deleted records and commit;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2456211

2. Ensure that enough undo data exists to rewind the table to the specified target.
SQL> SELECT NAME, VALUE/60 MINUTES_RETAINED
2 FROM V$PARAMETER
3 WHERE NAME = 'undo_retention';

NAME MINUTES_RETAINED
------------------------------ ----------------
undo_retention 60

3. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.
SQL> alter table SCOTT.EMP enable row movement;
Table altered.

4. Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.
SQL> SELECT other.owner, other.table_name
2 FROM sys.all_constraints this, sys.all_constraints other
3 WHERE this.owner = 'SCOTT'
4 AND this.table_name = 'EMP'
5 AND this.r_owner = other.owner
6 AND this.r_constraint_name = other.constraint_name
7 AND this.constraint_type='R';
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT DEPT

5. Execute a FLASHBACK TABLE statement for the objects that you want to flash back.
SQL> flashback table SCOTT.EMP
2 to timestamp to_timestamp ('2008-05-17 18:30:31','YYYY-MM-DD HH24:MI:SS');
Flashback complete.

NOTE: User performed delete operation at 18:30:31 so he flashback table with TIMESTAMP.

6. Optionally, query the table to check the data.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
13

NOTE: Flashback table operation is complete and lost data is recovered.

Keeping Triggers Enabled During Flashback Table

It is recommended to add ENABLE TRIGGER keyword with FLASHBACK TABLE statement. For enable database trigger which create on flashback table.

For example:

SQL> Flashback table SCOTT.EMP to timestamp to_timestamp (‘2008-05-17 18:30:31’,’YYYY-MM-DD HH24:MI:SS’) ENABLE TRIGGERS;