Objective:
With flashback feature you can recover your table or data, perform queries that return past data in Oracle 19c.
The Points to be noted that,The flashback cannot be usable when DDL or Truncate has been done in the table after removing the data.
How to do it:
select count(*) from emp_test;
/
select * from emp_test;
/
Result:
Deleting 1 record from Emp table:
delete from emp_test where empno=1;
commit;
/
Checking the data count before delete:
select count(*) from emp_test as of timestamp systimestamp-interval ‘5’ minute;
Result:
You can reach the deleted rows with AS OF clause. You can also use SCN which refers to System Change Number.
Finding SCN Number:(System Change Number.)
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP – INTERVAL ‘5’ MINUTE) SCN_COL FROM DUAL;
/
select count(*) from emp_test as of SCN 303521768263;
/
Result:
In order to Flash back a table ,we need to enable the row movement in the table where we need to recover the data.
ALTER TABLE emp_test ENABLE ROW MOVEMENT;
/
Flashback the data:
FLASHBACK TABLE emp_test TO SCN 303521768263;
/
After Flashback the data:
select count(*) from emp_test;
/
select * from emp_test;
/
Result:
Now,The data has been successfully recovered to the table,We need to act fast inorder to recover the data we removed wrongly.
