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

Home » Articles » 10g » Here

Flashback Table in Oracle Database 10g

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

Related articles.

Flashback Table

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

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.