Flashback Technology :#01

Flashback Table


Using Flashback Table to return Table to Past States.

With Flashback Table, you can recover a table or tables to a specific point in time without restoring a backup. When you use this feature, the data in tables and their associated objects (indexes,constraints, triggers, and so on) is restored. The data used to satisfy a Flashback Table request is retrieved from the undo tablespace. Therefore, UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state. You can only flash back tables up to the retention time you specified.You can use Flashback Versions Query and Flashback Transaction Query to determine the appropriate flashback time.

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.

You must grant the FLASHBACK TABLE or FLASHBACK ANY TABLE system privilege to any user that uses the Flashback Table feature. In addition, you must grant the SELECT, INSERT, DELETE, and ALTER object privileges to the user.


Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE emp ENABLE ROW MOVEMENT;



SQL> FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:12:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;  2    3
FLASHBACK TABLE EMP TO TIMESTAMP
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL>ALTER TABLE emp ENABLE ROW MOVEMENT;
SQL>
SQL> select * from emp;

     EMPNO NAME
---------- --------------------------------------------------
       111 ABDD
       222 BCFF
       333 DDGG
       444 EEE
       555 FFFF
       555 FFFF
       666 GGGG
       666 GGGG
       666 GGGG
       999 KUMAR

10 rows selected.

SQL> FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:12:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;  2    3

Flashback complete.

SQL>  select * from emp;

     EMPNO NAME
---------- --------------------------------------------------
       111 ABDD
       222 BCFF
       333 DDGG
       444 EEE
       555 FFFF
       555 FFFF
       666 GGGG
       666 GGGG
       666 GGGG

9 rows selected.

SQL>



FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:11:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;

You have to give ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.

Flashback Table cannot be performed on system tables, remote tables, and fixed tables.

Flashback table using SCN

SQL>
SQL> create table test_flash(id number);

Table created.

SQL> alter table test_flash enable row MOVEMENT;

Table altered.

SQL>
SQL>
SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    1628982

SQL> insert into test_flash values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    1629015

SQL> FLASHBACK TABLE test_flash TO SCN 1628982;

Flashback complete.

SQL> select * from test_flash;

no rows selected

SQL> FLASHBACK TABLE test_flash TO SCN 1629015;

Flashback complete.

SQL> select * from test_flash;

        ID
----------
         1
         1
         1

SQL>