Flashback Table in Oracle DBA

The FLASHBACK TABLE the 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;).

Syntax Of Flashback Table

flashback syntax

The following example creates a table, inserts some data, and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.

CREATE TABLE flashback_table_test (

id NUMBER(10)



SELECT current_scn FROM v$database;




INSERT INTO flashback_table_test (id) VALUES (1);


SELECT current_scn FROM v$database;




FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;




FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;




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');


Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

What is the Oracle Flashback Database?

Flashback Database is a part of the backup & recovery enhancements in Oracle 10g Database that are called Flashback Features. The main purpose of Flashback Technology is to let you quickly recover from logical corruptions or user errors. Oracle Flashback Features include the following:

  • Flashback DATABASE
  • Flashback DROP
  • Flashback TABLE
  • Flashback QUERY
  • Flashback VERSIONS QUERY

What is a flashback in Oracle?


Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. With flashback features, you can do the following: Perform queries that return past data.

Recovering Tables Using Oracle Flashback Table

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle DBA Interview Questions

Oracle Flashback Table:

  • Restores all data in a specified table to a previous point in the time described by a timestamp or SCN.
  • Performs the restore operation online.
  • Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.
  • Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.
  • Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.
  • Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

Benefits Of Flashback

Flashback Database provides:

  • A very effective way to recover from complex human errors
  • Faster database point-in-time recovery
  • Simplified management and administration
  • Little performance overhead

For in-depth knowledge on Oracle DBA click on: