FLASHBACK TABLE

FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error.

The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

SQL> CREATE TABLE flashback_table_test (id  NUMBER(10));

Table created.

SQL> ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

Table altered.




SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993733

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN          TO_CHAR(SYSTIMESTAMP
-----------         -------------------------
    4993746           2021-07-20 23:18:30

SQL> INSERT INTO flashback_table_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993843

SQL>FLASHBACK TABLE flashback_table_test TO SCN 4993733;

Flashback complete.

SQL> SELECT COUNT(*) FROM flashback_table_test;

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

SQL> FLASHBACK TABLE flashback_table_test TO SCN 4993843;
  
Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

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

SQL> FLASHBACK TABLE flashback_table_test TO TIMESTAMP 
TO_TIMESTAMP(' 2021-07-20 23:18:30', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8