How the Flashback Table Feature Works

Flashback Table uses undo information to restore data rows in changed blocks of tables with DML

statements like INSERT, UPDATE, and DELETE. Let’s review the steps in a Flashback Table operation.

■Note You can’t flash back any of the SYS user’s objects.

First, you need to make sure the user performing the Flashback Table operation has all privileges,

which could be either FLASHBACK ANY TABLE or the more specific FLASHBACK object

privilege on the table to be flashed back. The user must also have SELECT, INSERT, DELETE, and

ALTER privileges on the table.

The flashback operation doesn’t preserve Oracle ROWIDs when it restores rows in the changed

data blocks of the table, since it uses DML operations to perform its work. These DML operations

change the ROWIDs of the affected rows, so you must ensure that you have enabled row movement

in the tables you are using for the Flashback Table feature, as shown here:

SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;

Table altered.

SQL>

CHAPTER 8 ■ ORACLE TRANSACTION MANAGEMENT 377

Once you enable row movement in the table, you are ready to flash back the table to any time or

any SCN in the past, providing you have the necessary undo information in your undo tablespace.

Before you use the Flashback Table feature, note its complete syntax:

SQL> FLASHBACK TABLE

[schema.]table

[,[schema.]table] . . .

TO {{SCN|TIMESTAMP} expr

[{ENABLE|DISABLE}TRIGGERS ]

|BEFORE DROP[RENAME TO table]

};

In this chapter, you’ll only see the FLASHBACK TABLE . . . TO SCN|TIMESTAMP part of the FLASHBACK

TABLE statement. In the last line, BEFORE DROP refers to the FLASHBACK DROP feature, which is

covered in Chapter 16 in the discussion of database recovery techniques.

Here’s an example that shows how to flash back a table to a past SCN:

SQL> FLASHBACK TABLE emp TO SCN 5759290864;

Flashback complete.

SQL>

■Tip When a Flashback Table operation completes, all indexes that belong to the tables in the Flashback Table

list will have their indexes reverted to the time to which the tables are flashed back. However, the optimizer statistics

will still reflect the current data in the table.

You can also specify a time to flash back to, using a time stamp instead of an SCN, as shown here:

SQL> FLASHBACK TABLE persons TO TIMESTAMP TO_TIMESTAMP

('2008-01-30 07:00:00', 'YYYY-MM-DD HH24:MI:SS');

The preceding FLASHBACK TABLE command restores the persons table to 10:00 AM on April 5,

2005.

You can use the following statement to flash back a table by one day:

SQL> FLASHBACK TABLE persons to TIMESTAMP (SYDATE -1);

You can flash back more than one table at a time, as shown in the following example (but first

make sure you enable row movement for both tables):

SQL> FLASHBACK TABLE persons,person_orgs TO TIMESTAMP (SYSDATE -1)

The Flashback Table operation is an in-place, online operation and thus doesn’t involve taking

datafiles or tablespaces offline, unlike traditional point-in-time recovery. Oracle disables all relevant

triggers by default and reenables them upon completing the table recovery, though you can override

this by appending the ENABLE TRIGGERS clause to your FLASHBACK TABLE statement, as shown here:

SQL> FLASHBACK TABLE persons to TIMESTAMP TO_TIMESTAMP

('2009-04-05 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

ENABLE TRIGGERS;

If you don’t have sufficient undo data to flash back the table, you’ll get the error shown in Listing

8-6, which means that part of the undo information was overwritten. Unfortunately, the Flashback

Table feature can’t help you here, as it relies entirely on the presence of adequate undo information.

The only solution is to use a larger undo tablespace or enable the guaranteed undo retention feature

as explained in the “The UNDO_RETENTION Parameter” section, earlier in this chapter.

378 CHAPTER 8 ■ ORACLE TRANSACTION MANAGEMENT

Listing 8-6. Failure of a Flashback Table Operation

SQL> FLASHBACK TABLE emp,dept to TIMESTAMP (SYSDATE -1);

flashback table emp, dept to TIMESTAMP (SYSDATE -1)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-12801: error signaled in parallel query server P005

ORA-01555: snapshot too old: rollback segment number 108 with name

"_SYSSMU108$" too small

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too

small"

// *Cause: rollback records needed by a reader for consistent read are

// overwritten by other writers

// *Action: If in