In today’s article, I will be explaining how we can use the FLASHBACK TABLE statement to restore an earlier state of a table in case of an error.
1. We query the data of the table to be tested.
|
1 2 3 4 5 6 7 8 |
13:09:15 [Primary-2] SQL> select * from hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa |
2. DML operations are performed on the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
13:09:24 [Primary-2] SQL> update hr.regions set region_name='Middle East' where region_id=4; 1 row updated. 13:10:07 [Primary-2] SQL> commit; Commit complete. 13:10:11 [Primary-2] SQL> insert into hr.regions values(5,'Africa'); 1 row created. 13:11:11 [Primary-2] SQL> commit; Commit complete. |
3. We query the table’s data again.
|
1 2 3 4 5 6 7 8 9 |
13:11:19 [Primary-2] SQL> select * from hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East 5 Africa |
4. The table is flashback.
|
1 2 3 4 5 |
13:12:16 [Primary-2] SQL> flashback table hr.regions to timestamp to_timestamp('25/05/2018 13:05:00','DD/MM/YYYY HH24:MI:SS'); flashback table hr.regions to timestamp to_timestamp('25/05/2018 13:05:00','DD/MM/YYYY HH24:MI:SS') * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled |
5. Row movement is enabled in the table.
|
1 2 3 |
13:13:37 [Primary-2] SQL> alter table hr.regions enable row movement; Table altered. |
6. The painting is flashback.
|
1 2 3 |
13:15:57 [Primary-2] SQL> flashback table hr.regions to timestamp to_timestamp('23/05/2018 13:05:00','DD/MM/YYYY HH24:MI:SS'); Flashback complete. |
7. It is checked whether the table is back to its previous state.
|
1 2 3 4 5 6 7 8 |
13:16:11 [Primary-2] SQL> select * from hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa |
