Working with Flashback Table in Oracle DBA

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

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

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

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

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

SELECT current_scn FROM v$database;

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

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

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

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

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



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

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:

• Oracle Service BUS
• Working with Flashback Table in Oracle DBA
• Types of privileges of ‘SYSOPER’ in Oracle DBA