Databases Reference
In-Depth Information
The flashback versions query in Recipe 13-20 uses the information in the undo segments to display past versions
of the data at multiple points in time. Flashing back a table uses the same undo data to reconstruct the data at
whatever point in the past you specify. If sufficient information is not available in the undo segments, you will get the
following error:
SQL> flashback table accounts to timestamp to_date ('12-JUL-12 15.23.00', 'dd-MON-YY hh24.mi.ss');
flashback table accounts to timestamp to_date ('12-JUL-12 15.23.00', 'dd-MON-YY hh24.mi.ss')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P003
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small
This error may not be that intuitive to interpret, but it conveys the message—the undo segment does not have
information the flashback operation needs. In that case you can resort to Recipe 13-22 for an alternative mechanism
to get the table back from the past.
Contrast this operation with the flashback database operation. In flashback database, the changes at the block
level to the entire database are captured in flashback logs, and the flashback operation undoes the block changes. Any
database change—the creation of new objects, truncation, and so on—is captured by the logs and can be played back.
In a flashback query, the data is reconstructed from the undo segments. Any DDL operations are not reinstated. So if
you have added a column at 1:30 p.m. and flash back to 1:25 p.m., the added column is not dropped. By the way, the
DDL operation does not restrict your ability to perform a flashback beyond that point.
During the flashback operation, the database might have to move the rows from one block to another. This is
allowed only if the table has the property
row movement
enabled. Therefore, you had to enable that as the first step of
the process.
You can flash back a table owned by another user, but to do so you need
SELECT
,
INSERT
,
DELETE
, and
ALTER
privileges on the table, as well as one of the following:
FLASHBACK ANY TABLE
system privilege
•
FLASHBACK
privilege on that particular table
•
Table flashback does not work on the following types of tables:
•
Advanced queuing (AQ) tables
•
Individual table partitions or subpartitions
•
Materialized views
•
Nested tables
•
Object tables
•
Remote tables
•
Static data dictionary tables
•
System tables
•
Tables that are part of a cluster
Some restrictions are relaxed with newer versions of the Oracle Database, so it is possible that some items may
not be in this list when you read this topic.
