Rewinding Tables with FLASHBACK TABLE
While Flashback Version Query and Flashback Transaction
Query offer the capability to retrieve the state of a table's rows at a prior
point in time, Oracle 10g also offers the ability to restore an entire table
to an earlier state within the boundaries of available UNDO data via the FLASHBACK TABLE
command.
To illustrate this, I will create a new table in the HR demo
schema called APPLICANTS
that I will use to record information about each person applying for a job. I
will use a row-level trigger and a sequence to automatically increment the
primary key column, APPLICANTS.APPLICANT_ID,
whenever a new entry is added to the table.
Listing
3.6 shows the DDL and DML statements necessary to create and populate
this table initially. Once the new table was populated, I recorded the maximum
SCN (2177093) just before I issued a series of additional INSERT statements
shown in Listing
3.7.
I will issue the FLASHBACK TABLE command shown in Listing 3.8 to
bring the table back to its initial state. Note that I set the table's ENABLE ROW MOVEMENT
parameter to TRUE before attempting to "rewind" the table.
Prerequisities. Before I can execute a FLASHBACK TABLE
command, there are some precursors:
-
The UNDO segments that hold the statements needed to "rewind" the
table(s) back to its prior state must still be available.
-
The user account from which I am issuing the FLASHBACK TABLE
command must have been granted the FLASHBACK TABLE object privilege for the tables
that I wish to "rewind," or the user account must have been granted the FLASHBACK ANY TABLE
privilege.
-
Also, the user account that is performing the FLASHBACK TABLE
operation must have been granted SELECT, INSERT, UPDATE, and DELETE rights.
-
Finally, the table(s) to be "rewound" must have the ENABLE ROW MOVEMENT
directive enabled. This directive allows Oracle to move rows into or out of the
selected table(s).
Caveats. Even though FLASHBACK TABLE offers some
slick new capabilities, some warnings are in order:
-
It is important to remember that once the FLASHBACK TABLE
operation is completed, it only rolls back the transactions applied to the
table or tables specified in the command. However, the state of any other
database objects is unchanged. If I now reissue the second set of INSERTs,
the sequence upon which the APPLICANT table's BEFORE INSERT trigger has not
been reset, and the next set of applicants will use the most current value of
the sequence for the APPLICANT_ID value.
-
A FLASHBACK TABLE operation cannot be rolled back, as an implicit
COMMIT is issued once it is complete. However, another FLASHBACK TABLE
statement can be issued to restore the table to a different point in time
(providing, of course, that sufficient UNDO is available for the successive
operation).
-
Also, FLASHBACK TABLE cannot be used to recover to a point in
time prior to the issuance of DDL statements that have modified the table's
structure.
Restoring a Dropped Table: FLASHBACK DROP and the Recycle Bin
Rounding out the new logical flashback features, Oracle 10g
offers the capability to recover from one of the most destructive "accidental"
operations that can happen to any Oracle database: the complete removal
of a table via the DROP TABLE
command. To facilitate this new feature, Oracle 10g has added a new storage
area to the database called the Recycle Bin where dropped objects are
retained until the object is either recovered via a Flashback Drop operation,
or until the object is purged from the Recycle Bin.
Peering Into the Recycle Bin. Every time a table is
dropped, it is assigned a unique object identifier in the Recycle Bin.
This 30-character-long object identifier is in the format BIN$$globalUID$version,
where globalUID is
a 24-character globally unique identifier for the dropped object, and version is
assigned for each version of the dropped object.
Oracle 10g provides several methods to view the Recycle
Bin's contents and identify which tables have been dropped:
-
A new column, DROPPED,
has been added to the DBA_TABLES
data dictionary view to allow screening for tables that have been dropped from
the database but are now present in the Recycle Bin instead.
-
The SHOW RECYCLEBIN; command
shows all dropped tables and their dependent objects when issued from within a
SQL*Plus session.
-
The RECYCLEBIN
data dictionary view shows the contents of the Recycle Bin for the current
user.
-
Finally, the DBA_RECYCLEBIN
data dictionary view shows the complete contents of the Recycle Bin.
Viewing Different Versions of Dropped Tables. Even if
a table is created and dropped several times, all of the different iterations
of the dropped table and its dependent objects are retained in the Recycle Bin
until they are purged. Using the dropped table's object identifier, I can query
directly against the dropped table's data in the Recycle Bin, thus allowing me
to determine exactly which version of the table should be recovered. I will
demonstrate this feature in an upcoming recovery example.
Listing
3.9 displays several examples of querying the Recycle Bin for its
current status.
Recycle Bin Space Pressure and Automatic Purging.
Oracle 10g automatically manages the contents of the Recycle Bin to insure
there is enough space to store any dropped tables and their related objects.
Unfortunately, this also means that there is no way to predict when Oracle may
need to purge objects from the Recycle Bin.
Oracle will keep objects in the Recycle Bin until it can no
longer allocate new extents in the tablespace where the dropped objects
originally resided without expanding the tablespace. This situation is known as
space pressure. When space pressure demands that Recycle Bin space be
reclaimed, Oracle will purge the oldest objects first (i.e., first-in,
first-out basis), and it will purge a dropped table's dependent objects first
(e.g. indexes, triggers) before it purges the table itself.
Manually Purging Recycle Bin Objects. Versions of
objects that no longer need to be retained can also be purged manually via the
following commands, in order of increasing destructiveness to the Recycle Bin:
-
Purging A Single Index. The PURGE INDEX <index name>; command purges
the most recent incarnation of the specified index from the Recycle Bin. Note that
the index cannot have been used to enforce a constraint for its supported table;
otherwise, Oracle will return an error.
-
Purging A Single Table. Issuing the PURGE TABLE <table name>; purges only the
most recent incarnation of the dropped table and its dependent objects (e.g.
indexes and triggers).
-
Purging All Objects in a Tablespace. The PURGE TABLESPACE <tablespace_name>; purges
all objects in the specified tablespace from the Recycle Bin.
-
Purging All Schema Objects. The PURGE RECYCLEBIN; command will purge all schema objects for the
current user account from the Recycle Bin.
-
Purging All Objects. Finally, the PURGE DBA_RECYCLEBIN; command purges all database
objects in the Recycle Bin. Note that this command must be issued from a user account
with DBA privileges.
See Listing
3.10 for examples of these commands.
Example: Restoring a Dropped Table. As long as the
table and its dependent objects are still present in the Recycle Bin, the table
can be recovered using the FLASHBACK
TABLE <table name> TO BEFORE DROP; command. To
illustrate the power of this new feature, I have dropped the HR.APPLICANTS
table created in the previous FLASHBACK
TABLE example and then purged the entire Recycle Bin via the PURGE DBA_RECYCLEBIN; command.
Next, I recreated the table via the code in Listing 3.6, and
loaded with the first set of applicants. I dropped it again,
created it again and loaded it with the first set as well as the second set of
applicants (Listing
3.7). Finally, I dropped the table a third time, reloaded it with the
first and second set of applicants, added a third set of applicants (Listing 3.11),
and dropped it once again. This left three distinct iterations of the table to
experiment against. Here are the results stored in the Recycle Bin after these
operations:
SQL> TTITLE 'Current Recycle Bin Contents'
SQL> COL object_name FORMAT A30 HEADING 'Object Name'
SQL> COL type FORMAT A8 HEADING 'Object|Type'
SQL> COL original_name FORMAT A20 HEADING 'Original Name'
SQL> COL droptime FORMAT A20 HEADING 'Dropped On'
SQL> SELECT
2 object_name
3 ,type
4 ,original_name
5 ,droptime
6 FROM dba_recyclebin
7 WHERE owner = 'HR'
8 ;
Current Recycle Bin Contents
Object
Object Name Type Original Name Dropped On
------------------------------ -------- -------------------- --------------------
BIN$GXKs4x3zS+6aEyHIbjIO0g==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:04:25
_IDX
BIN$0YQGF9xpTgOPRqsqfuHtNA==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:03:36
_IDX
BIN$lpBfdfPSQfai8dZoa/DHUw==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:03:36
BIN$TdmwJaPjSIu5XTGn2vmweQ==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:04:25
BIN$eUzM3ZWMTQefYskd+7kAqw==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:04:25
BIN$ldrmjTN0R8K8qyRRCmSsxw==$0 TABLE APPLICANTS 2005-01-3:19:04:25
BIN$RSEdFMhCRcqCv5g7lYss6A==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:03:36
BIN$992SjQhHRlqHZHB4Aa/dWQ==$0 TABLE APPLICANTS 2005-01-3:19:03:36
BIN$tqINHzsMRT6EfbsgiD8eFQ==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:06:56
_IDX
BIN$877/hfooRKuuiVAKjsE7Jg==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:06:56
BIN$vLf00KzHSpGbMjkhnJETUw==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:06:56
BIN$xJfp8JRWQ9KalGzPUw9Ygg==$0 TABLE APPLICANTS 2005-01-3:19:06:56
12 rows selected.
By querying directly against the Recycle Bin using the
dropped table's object identifier, I can confirm that the second iteration of
the HR.APPLICANTS table has exactly 30 rows:
SQL> -- Most recent iteration of HR.APPLICANTS
SQL> SELECT COUNT(*) FROM "BIN$xJfp8JRWQ9KalGzPUw9Ygg==$0";
COUNT(*)
----------
45
SQL> -- Second-most recent iteration of HR.APPLICANTS
SQL> SELECT COUNT(*) FROM "BIN$ldrmjTN0R8K8qyRRCmSsxw==$0";
COUNT(*)
----------
30
SQL> -- Third-most recent iteration of HR.APPLICANTS
SQL> SELECT COUNT(*) FROM "BIN$992SjQhHRlqHZHB4Aa/dWQ==$0";
COUNT(*)
----------
16
Oracle retrieves the most recently-dropped table first, so
if I issued a FLASHBACK TABLE hr.applicants TO BEFORE DROP;
Oracle would restore the iteration with 45 rows. Since I want to restore only
the iteration with 30 rows, I will issue a FLASHBACK TABLE <object_identifier> TO BEFORE DROP; command to insure that I
have restored the desired copy of the table:
FLASHBACK TABLE "BIN$ldrmjTN0R8K8qyRRCmSsxw==$0" TO BEFORE DROP;
Alternatively, I can restore a different iteration of the
table as with a different object name:
FLASHBACK TABLE "BIN$992SjQhHRlqHZHB4Aa/dWQ==$0"
TO BEFORE DROP
RENAME TO applicants_1;
Conclusion
Oracle 10g's new Logical Flashback features significantly
expand an Oracle DBA's abilities to recover data, transactions and database
objects that have been lost with a minimum of effort. When these new features
are used in conjunction with each other and with the FLASHBACK DATABASE
features described in the previous article, just about any data loss situation
can be forestalled. The next article the final one in this series -- will
concentrate on additional availability enhancements implemented as part of DataGuard
and Logminer.
References and Additional Reading
While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle
10g documentation for the deeper technical details of this article:
B10734-01 Oracle Database
Backup and Recovery Advanced User's Guide
B10735-01 Oracle Database
Backup and Recovery Basics
B10750-01 Oracle Database
New Features Guide
B10759-01 Oracle Database
SQL Reference
B10770-01 Oracle Database
Recovery Manager Reference
»
See All Articles by Columnist Jim Czuprynski
» 
Comment and Contribute