Flashback concept of Oracle uses the Automatic Undo Management system to obtain rows of a table for any transaction. It depends on undo data, which are records of the effects of individual transactions. Example, if a user edits to change a salary from 1500 to 2000, then Oracle Database stores the value 1500 in the undo data.
To use Oracle Flashback Query, use SELECT statement with AS OF clause. Oracle Flashback Query retrieves data as it existed at some time earlier. The query explicitly refers earlier time through a timestamp clause or System Change Number (SCN). It returns committed data that was current at that point of time.
Uses of Oracle Flashback Query include:
- Recovering lost data using deleted data or modified data, committed changes.
For example, if by mistake a row is deleted or updated, then commit the transaction, the mistake can be immediately undone.
Matching current data with the corresponding data at some time in the past, for example, one can run a daily report that shows the change in data from the recent past. One can compare discrete rows of table data or find intersections or unions of sets of rows.
- Checking the state of transactional data at a certain time. For example, one can verify the account balance of a certain day.
- Shortening application design by removing the need to store some kinds of temporary data.
- Oracle Flashback Query lets one recover past data straight from the database.
Now it’s time to explore our workshop for flashback query:
At first install Oracle 10g Express which is a free version for individual use, after that write the following commands to test the Flashback Query.
Log-in as SYSDBA
Create a user named SCOTT because in Oracle 10g Express edition no such user present
SQL> CREATE USER SCOTT IDENTIFIED BY TIGER;
SQL> GRANT CONNECT, RESOURCE TO SCOTT;
SQL> GRANT SELECT, UPDATE, DELETE, INSERT ON HR.EMPLOYEES TO SCOTT;
Here HR is another user who owned the table Employees and here SYSDBA grant permission to SCOTT to use that table of HR.
SQL> GRANT FLASHBACK ON HR.EMPLOYEES TO SCOTT;
SQL> GRANT SELECT ANY TRANSACTION TO SCOTT;
Now log-in as SCOTT/TIGER and from SCOTT user issue the following commands:
SQL> DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (195,196);
2 record(s) deleted
SQL> SELECT EMPLOYEE_ID FROM HR.EMPLOYEES;
In the above list EMPLOYEE_ID number 195 and 196 are not present, that is, these two are deleted permanently from that table.
Now use the following command to show the flashback of the previous table data.
SQL> SELECT EMPLOYEE_ID FROM HR.EMPLOYEES AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL ‘120’ MINUTE WHERE HR.EMPLOYEES.EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM HR.EMPLOYEES);
SYSTIMESTAMP refers to the time zone of the database host environment and –INTERVAL ‘120’ MINUTE refers 120 minutes before the current time.
Here the query displays the deleted data (rows).
This is called Flashback Query in Oracle.