Database Reference
In-Depth Information
Use Flashback to Query Data
In the previous section you learned how to restore tables that had been dropped by accident.
But oftentimes logical corruption issues are not as blatant as a dropped table. It is much more
common that a user will make a mistake on a data-entry form, delete a row by accident, or
make any of a number of other mistakes. When a problem like this occurs, wouldn't it be nice
to be able to go back in time to fix it? Flashback Query allows you to do just that—in a man-
ner of speaking.
Flashback Query
provides a method of viewing data as it existed at a prior point in time.
So when a user makes a mistake, you can just go back in time and fix it.
Flashback Query is implemented through the
AS OF
clause of the
SELECT
statement. The
AS OF
clause is used to specify a particular point in time (either a timestamp or an SCN for
one or more tables in the query. When the query is executed, Flashback Query will return the
data exactly as it existed at a specified point in time. It is important to note that Flashback
Query will return only committed data. It will never return uncommitted data. So if the
query happens to specify a point in time that falls in the middle of a transaction, Flashback
Query will ignore the transaction and simply return the committed data.
As you can imagine, Flashback Query can be used in many situations encountered by a
DBA in the course of their duties:
■
Recovering from data changes that were committed by mistake
■
Comparing current data values to past values
■
Simplifying certain programming tasks by alleviating the need to store certain types of
temporary data
■
Allowing users to correct their own mistakes
In the past, these types of problems could be addressed only through a costly and time-
consuming recovery process. With Flashback Query, they can be handled with ease. Also,
Flashback Query functionality is not limited to the DBA. Any user who has been granted
SELECT
and
FLASHBACK
privileges can take advantage of it.
Let's take a look at Flashback Query in action. To begin with, we will look at the
JOB_HISTORY
table in the HR schema:
SQL> select * from hr.job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-01 24-JUL-06 IT_PROG 60
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110
201 17-FEB-04 19-DEC-07 MK_REP 20
114 24-MAR-06 31-DEC-07 ST_CLERK 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
200 17-SEP-95 17-JUN-01 AD_ASST 90
176 24-MAR-06 31-DEC-06 SA_REP 80
Search WWH ::
Custom Search