In today’s article, we will be learning how to Rewind A Table Using Oracle Flashback Table.
1. We query the data in the table.
|
1 2 3 4 5 6 |
SQL> set time on 16:30:33 SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8469 |
2. We determine the data to be deleted.
|
1 2 3 4 5 |
16:30:44 SQL> select count(*) from oardahanli.test where username = 'DBSNMP'; COUNT(*) ---------- 8107 |
3. We delete the data.
|
1 2 3 4 5 6 7 |
16:30:54 SQL> delete oardahanli.test where username = 'DBSNMP'; 8107 rows deleted. 16:30:59 SQL> commit; Commit complete. |
4. Let’s add new data to the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
16:32:14 SQL> insert into oardahanli.test select * from dba_audit_trail where username not in ('DBSNMP'); 374 rows created. 16:33:14 SQL> commit; Commit complete. 16:33:20 SQL> insert into oardahanli.test select * from dba_audit_trail where username not in ('DBSNMP'); 374 rows created. 16:33:27 SQL> commit; Commit complete. 16:33:31 SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 1110 |
5. Let’s want to bring the table back before the deletion process.
|
1 2 3 4 5 |
16:33:54 SQL> flashback table oardahanli.test to timestamp to_timestamp('23-02-2016 16:30:00','DD-MM-YYYY HH24:MI:SS'); flashback table oardahanli.test to timestamp to_timestamp('23-02-2016 16:30:00','DD-MM-YYYY HH24:MI:SS') * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled |
6. We enable Row Movement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
16:35:52 SQL> SELECT table_name, row_movement from dba_tables where table_name ='TEST'; TABLE_NAME ROW_MOVE ------------------------------ -------- TEST DISABLED 16:37:04 SQL> alter table oardahanli.test enable row movement; Table altered. 16:37:29 SQL> SELECT table_name, row_movement from dba_tables where table_name ='TEST'; TABLE_NAME ROW_MOVE ------------------------------ -------- TEST ENABLED |
7. It is brought to just before the time to delete the table data.
|
1 2 3 |
16:37:35 SQL> flashback table oardahanli.test to timestamp to_timestamp('23-02-2016 16:30:00','DD-MM-YYYY HH24:MI:SS'); Flashback complete. |
8. Data is queried.
|
1 2 3 4 5 |
16:37:43 SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8469 |
