How to Use Flashback Query in Oracle 12c

By Chris Ruel, Michael Wessler

Have you ever wanted a time machine? No such thing exists. Or does it? The Oracle 12c time machine known as Flashback lets you rewind, fast forward, and recover from situations with ease. Flashback can sound intimidating, but the feature is simple.

A feature called Flashback Query is one of the simplest and easiest to use variations of the Oracle Flashback technology. Simply put, it allows you to query a table at a point in the past. This means that, despite any updates, inserts, and deletes that may have happened, you see the table as it existed at the point in time you choose.

Here’s a quick demo with the example schema, HR:

  1. Log in to SQL*Plus as the HR user.

  2. Look at the data in your departments table.

  3. Restrict the query to both minimize output and make the demo more obvious. Type the following:

    <select department_id, department_name
    from departments where manager_id is null;>

    You should see something like this:

    DEPARTMENT_ID DEPARTMENT_NAME
    ------------- ------------------------------
         120 Treasury
         130 Corporate Tax
         140 Control And Credit
         150 Shareholder Services
         160 Benefits
         170 Manufacturing
         180 Construction
         190 Contracting
         200 Operations
         210 IT Support
         220 NOC
         230 IT Helpdesk
         240 Government Sales
         250 Retail Sales
         260 Recruiting
         270 Payroll
    16 rows selected.
  4. Modify the table by removing the rows selected in the preceding output. To remove the rows, type

    <delete from departments
    where manager_id is null;>

    You should see this:

    16 rows deleted.
  5. Commit your changes by typing

    <commit;>

    You should see this:

    Commit complete.
  6. Run the original query again.

    You should see this:

    no rows selected

    The next step is where you use the magic of Flashback Query. Think of the time before the delete occurred. In this example case, it was five minutes ago.

  7. Type something similar to the following to see the data as it existed five minutes ago:

<select department_id, department_name
from departments
AS OF TIMESTAMP SYSDATE – 1/288
where manager_id is null;>

You see the rows as they existed in Step 2. The key is the AS OF TIMESTAMP clause. For the target of the timestamp, you can see that the example uses math on the SYSDATE function. SYSDATE represents the current time, right now.

Subtracting 1 from SYSDATE means yesterday. The example subtracts the fraction 1/288, which means five minutes, from SYSDATE. To get that fraction, the example uses the following formula:

24 (hours in a days) × 60 (minutes in and hour) = 1440 (minutes) in a day

So, 5 over 1440 is equal to 1 over 288.

You can also use an explicit timestamp instead of a SYSDATE function. For example:

select department_id, department_name
from departments
AS OF TIMESTAMP TO_DATE('16-AUG-2013 20:04:00','DD-MON-YYYY HH24:MI:SS')
where manager_id is null;

Here’s a neat trick. Suppose you accidentally deleted those rows and want to insert them back into your table. You can use Flashback Query to do such a thing. This is going to be much faster than doing an RMAN recovery to a point in time.

To insert your deleted rows back into your table, type

< insert into departments
select *
from departments
AS OF TIMESTAMP TO_DATE('16-AUG-2013 20:04:00','DD-MON-YYYY HH24:MI:SS')
where manager_id is null;>

You should see this:

16 rows created.

Query the table and, when you’re satisfied, commit your changes.

You can only flash back as far as your setting for the instance parameter undo_retention. By default, this parameter is set to 15 minutes (900 seconds). If the time you wish to go back to is further than your undo_retention, you may not be able to see your data and will receive an error instead.

Before you take the previous tip and set your undo_retention to reflect a month’s worth of time, consider that this will cause your UNDO tablespace to grow because it has to store images of your old data. You should carefully balance a realistic undo_retention setting with space consumption.