8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Flashback Table in Oracle Database 10g

The FLASHBACK TABLE command allows a type of point-in-time recovery (PITR) of individual tables.

Flashback Table
Considerations

Flashback Table

The FLASHBACK TABLE command allows point-in-time recovery of individual tables subject to the following requirements.

You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

Create a test table, enable row movement and check the SCN of the database.

CREATE TABLE flashback_table_test (
  id  NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715315

SQL>

Create a new row in the test table, commit the change and check the SCN again.

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715340

SQL>

Flashback the table to the point in time represented by the first SCN, before the row was created, and check the contents of the table.

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         0

SQL>

Flashback the table to the point in time represented by the second SCN, after the row was created, and check the contents of the table.

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

SQL>

Flashback of tables can also be performed using timestamps.

FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

Considerations

There are a few things to consider when using flashback table.

Flashback table is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.
The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.
Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.
Although the end product looks like a point-in-time recovery (PITR), this is an undo-based operation, which doesn't require the use of backups or flashback logs. The rows are inserted, updated or deleted to replay the actions required to bring the data to the relevant point in time. This can itself generate a lot of undo and redo.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.