How to Use Oracle 12c’s Flashback Table - dummies

How to Use Oracle 12c’s Flashback Table

By Chris Ruel, Michael Wessler

Very similar to Flashback Query is Oracle 12c’s Flashback Table. In fact, both are built on the same underlying technology. Flashback Table is capable of flashing back row changes or the entire table if it was accidentally dropped.

Keep the following requirements in mind:

  • The UNDO_RETENTION parameter is going to control how far back you can flash the table.

  • You need to use a feature called row movement that must be enabled on any table you want to flash back rows. (The upcoming steps show you how to enable row movement.)

  • If you want to be able to flash back a table from a drop, you need to have your parameter RECYCLEBIN set to ON. This parameter is turned on by default, but note that if it has been turned off it requires a database bounce to turn it back on.

Consider an example about accidentally deleting some rows from a table. They were very easy to recover because identifying them wasn’t difficult. However, suppose you want to recover from an accidental update. Updates are harder to recover from with Flashback Query. You can do it, but you may be able to make it easier on yourself by flashing back the entire table.

Flashback Table works on the entire table, not just certain rows, so make sure that bringing the entire table back in time is not going to cause other problems.

To use Flashback Table, follow these steps:

  1. You must have row movement enabled; to enable it, type

    <alter table departments enable row movement;>

    You should see this:

    Table altered.

    As a side note, you don’t have to enable row movement before you change your data. You can do it after the fact.

  2. See what your data looks like by typing

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

    You should see something like this:

    DEPARTMENT_NAME        MANAGER_ID
    ------------------------------ ----------
    Administration            200
    Marketing               201
    Purchasing              114
    Human Resources            203
    Shipping               121
    IT                  103
    Public Relations           204
    Sales                 145
    Executive               100
    Finance                108
    Accounting              205
    11 rows selected.

    To “mess up” the data, type the following:

    < update departments
    set manager_id = 205
    where manager_id is not null;
    commit;>

    You shoud see something like this:

    11 rows updated.
    Commit complete.
  3. Look at your data again by typing

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

    You should see something like this:

    DEPARTMENT_NAME        MANAGER_ID
    ------------------------------ ----------
    Administration            205
    Marketing               205
    Purchasing              205
    Human Resources            205
    Shipping               205
    IT                  205
    Public Relations           205
    Sales                 205
    Executive               205
    Finance                205
    Accounting              205
    11 rows selected.
  4. To flashback all the changes on the rows, type

    <flashback table departments
    to timestamp TO_DATE('16-AUG-2013 20:40:00','DD-MON-YYYY HH24:MI:SS');>

    You should see something like this:

    Flashback complete.
  5. Check your data again by typing

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

    You should see something like this:

DEPARTMENT_NAME        MANAGER_ID
------------------------------ ----------
Administration            200
Marketing               201
Purchasing              114
Human Resources            203
Shipping               121
IT                  103
Public Relations           204
Sales                 145
Executive               100
Finance                108
Accounting              205
11 rows selected.

The data is back where it was at the timestamp specified.

The next exercise helps you if you accidentally dropped your table. It utilizes the feature mentioned above called the Recyclebin. It functions much like the Recycle Bin on a Windows desktop. You can restore an object to what it looked like before it was dropped.

For this next example, you start by making a copy of the employees table to work with. The reason you’re making a copy is that in the demo schema provided with the database there is a lot of referential integrity, which prevents you from dropping tables in the first place (one of the many reasons referential integrity is such a good idea!).

  1. To make a copy of your employees table, type

    <create table emp as select * from employees;>

    You see something like this:

    Table created.
  2. “Accidentally” drop the new emp table by typing

    <drop table emp>;

    You see something like this:

    Table dropped.
  3. Query your Recyclebin to see what you can recover — type

    < select object_name, original_name, operation, droptime
    from user_recyclebin;>

    You see something like this:

    OBJECT_NAME          ORIGINAL_NAME  OPERATION DROPTIME
    ------------------------------ --------------- --------- -------------------
    BIN$5BtB5dF6GmXgQ1ABqMC8yg==$0 EMP       DROP   2013-08-16:21:02:12

    Notice the funny name starting with BIN$. With the Recyclebin turned on, what’s actually happening during a drop is the object is being renamed. You can still query the original table by using this name!

    This might be useful if you want to restore just a few rows rather than the entire table. You can query those rows or do a “create table as select” to store them in a new table altogether.

  4. To undrop your table, type

    < flashback table emp to before drop;>

    You see this:

    Flashback complete.

    You can now query your table again.

Although flashing back the table to before a drop does put back all privileges and indexes, the indexes keep the BIN$ name. You can give your indexes back the original name with a rename command if you want.