Understanding Flashback Technology - OCP: Oracle Database 12c Administrator Certified Professional - page 379

Perform Flashback Table Operations

All of the previous Flashback options we've covered in this chapter have allowed you to
view and correct specific data elements within a table. They have not affected the table as
a whole. Flashback Table is a little different in that regard.
Flashback Table is a Flashback technology that allows you to recover an entire table
(or set of tables) to a specific point in time without the hassle of performing an incomplete
recovery. This means that rather than rolling back a single transaction, the entire table will
be rolled back. If the table has dependent objects associated with it, they are also rolled
back automatically.
So why would you choose to use Flashback Table instead of performing an incomplete
recovery? There are several reasons:
Speed It is much faster than incomplete recovery.
Simplicity It is much easier than incomplete recovery.
Availability Flashback Table does not impact the availability of the database. Unlike
other recovery methods, the database remains available, and the tablespace remains online
the entire time.
Accessibility Users can flash back their own tables, so DBA involvement is not required.
Like other Flashback technologies, Flashback Table is limited only by the availability
of undo data. Flashback Table also uses RETENTION GUARANTEE in the same manner as the
previously discussed Flashback options.
There are two main clauses that are used with the Flashback Table:

The TO SCN clause can recover the Flashback Table to a certain SCN.

■

The TO TIMESTAMP clause can recover the Flashback Table to a certain point in time.

■

To flash back a table, the table must have ROW MOVEMENT enabled. This can
be accomplished with the following command: ALTER TABLE tablename
ENABLE ROW MOVEMENT .

It is important to get the current SCN from the database. The current SCN can be identi-
fied by querying the CURRENT_SCN column in the V$DATABASE view. To show that Flashback
Table is recovered, you can create a change to the data. In the following example, you will
update the SALARY value for an employee and commit the transaction. Then you will perform
a Flashback Table operation to recover the table to its state prior to the update. This change
will be missing if the table is recovered to an SCN before the change is introduced.
Let's walk through performing a Flashback Table operation with SCN:
1. Enable row movement on the employees table:

SQL> alter table employees enable row movement;

Table altered.