6.6. Using Flashback Table

Flashback Table is a Flashback Technology that allows you to recover a table or set tables to a specific point-in-time without performing an incomplete recovery. All dependent objects are also recovered when using Flashback Table.

Flashback Table has some significant benefits over incomplete recovery:

  • It is much faster and easier to use than incomplete recovery.

  • Flashback Table does not impact the availability of the database.

  • The DBA is not required to perform Flashback Table, so users can quickly recover from logical corruptions.

Like other Flashback Technologies, the Flashback Table is based on undo data and utilizes the UNDO_RETENTION initialization parameter to determine the amount of time to retain committed undo data in the database. The Flashback Table also uses RETENTION GUARANTEE in the same manner as the previously discussed Flashback Versions Query and Flashback Transaction Query.

NOTE

The privilege required to use Flashback Table is the system privilege FLASHBACK ANY TABLE or FLASHBACK TABLE. You must also grant SELECT, INSERT, DELETE, and ALTER object privileges to the user performing the Flashback Table.

You can use the Flashback Table with either Enterprise Manager or with standard SQL commands. In the earlier section titled "Using EM to Perform Flashback Dropped Tables," we used EM to perform this. 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.

NOTE

The Flashback Table must have ROW MOVEMENT enabled with the following command: ALTER TABLE tablename ENABLE ROW MOVEMENT.

It is important to get the current SCN number from the database. The current SCN can be identified 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 for JONES to 50000 and then commit the transaction. Then you can Flashback Table to an SCN prior to the change for employee JONES. 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 with SCN:

  1. Enable ROW MOVEMENT on table T1:

    SQL> alter table t1 enable row movement;

Table altered.

SQL>

  2. Retrieve the current SCN before you modify the table:

    SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     771511

SQL>

  3. Update a value in the table so you can verify the change was eliminated after you performed the Flashback Table operation to the SCN prior to the update:

    SQL> update t1 set salary=50000 where employee = 'JONES';

1 row updated.

SQL> commit;

Commit complete.

SQL>

  4. Perform the FLASHBACK TABLE command to recover the table to an SCN to a point-in-time before the update of JONES to a SALARY of 50000:

    SQL> flashback table t1
  2  to scn 771511;

  5. Query the table to verify the change was eliminated due to the Flashback Table to an SCN prior to the existence of the change:

    SQL> select * from tl  where employee='JONES';

EMPLOYEE                 SALARY
-------------------- ----------
JONES                     41000

SQL>

Triggers are disabled by default during the Flashback Table process. Triggers can be enabled with the ENABLE TRIGGERS option on the FLASHBACK TABLE command. This option enables all triggers that were enabled on the table before they were disabled by the Flashback Table operation. If you want certain triggers to be disabled, you can disable them with the ALTER TRIGGER command prior to performing a Flashback Table and then use the ENABLE TRIIGGERS clause.

Let's take a look at this Flashback Table option:

SQL> flashback table table_name
    2         to sen 771551
    3         enable triggers;

As you can see, the Flashback Table operation is a valuable recovery method. You might notice some similarity with Flashback Drop. Flashback Table is best used to recover a table that was updated with an incorrect WHERE clause. This can allow users to quickly and easily recover their own problems, without involving the DBA. In addition, the availability of the database is not compromised during the Flashback Table operation.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset