|
Arup
Nanda is the author of
Oracle Privacy Security Auditing by
Rampant TechPress.
Flashback Table
Arup Nanda
OTN
Reinstating an accidentally dropped table is effortless using
the Flashback Table feature in Oracle Database 10g
Here's a scenario that happens more often than it should: a user
drops a very important table--accidentally, of course--and it
needs to be revived as soon as possible. (In some cases, this
unfortunate user may even have been you, the DBA!)
Oracle9i Database introduced the concept of a Flashback Query
option to retrieve data from a point in time in the past, but it
can't flash back DDL operations such as dropping a table. The
only recourse is to use tablespace point-in-time recovery in a
different database and then recreate the table in the current
database using export/import or some other method. This
procedure demands significant DBA effort as well as precious
time, not to mention the use of a different database for
cloning.
Enter the Flashback Table feature in Oracle Database 10g, which
makes the revival of a dropped table as easy as the execution of
a few statements. Let's see how this feature works.
Drop That Table!
First, let's see the tables in the present schema.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE
Now, we accidentally drop the table:
SQL> drop table recycletest;
Table dropped.
Let's check the status of the table now.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
The table RECYCLETEST is gone but note the presence of the new
table BIN$04LhcpndanfgMAAAAAANPw==$0. Here's what happened: The
dropped table RECYCLETEST, instead of completely disappearing,
was renamed to a system-defined name. It stays in the same
tablespace, with the same structure as that of the original
table. If there are indexes or triggers defined on the table,
they are renamed too, using the same naming convention used by
the table. Any dependent sources such as procedures are
invalidated; the triggers and indexes of the original table are
instead placed on the renamed table
BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object
structure of the dropped table.
The table and its associated objects are placed in a logical
container known as the "recycle bin," which is similar to the
one in your PC. However, the objects are not moved from the
tablespace they were in earlier; they still occupy the space
there. The recycle bin is merely a logical structure that
catalogs the dropped objects. Use the following command from the
SQL*Plus prompt to see its content (you'll need SQL*Plus 10.1 to
do this):
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------
------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
2004-02-16:21:13:31
This shows the original name of the table, RECYCLETEST, as well
as the new name in the recycle bin, which has the same name as
the new table we saw created after the drop. (Note: the exact
name may differ by platform.) To reinstate the table, all you
have to do is use the FLASHBACK TABLE command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE
Voila! The table is reinstated effortlessly. If you check the
recycle bin now, it will be empty.
Remember, placing tables in the recycle bin does not free up
space in the original tablespace. To free the space, you need to
purge the bin using:
PURGE RECYCLEBIN;
But what if you want to drop the table completely, without
needing a flashback feature? In that case, you can drop it
permanently using:
DROP TABLE RECYCLETEST PURGE;
This command will not rename the table to the recycle bin name;
rather, it will be deleted permanently, as it would have been
pre-10g.
Managing the Recycle Bin
If the tables are not really dropped in this process--therefore
not releasing the tablespace--what happens when the dropped
objects take up all of that space?
The answer is simple: that situation does not even arise. When a
tablespace is completely filled up with recycle bin data such
that the datafiles have to extend to make room for more data,
the tablespace is said to be under "space pressure." In that
scenario, objects are automatically purged from the recycle bin
in a first-in-first-out manner. The dependent objects (such as
indexes) are removed before a table is removed.
Similarly, space pressure can occur with user quotas as defined
for a particular tablespace. The tablespace may have enough free
space, but the user may be running out of his or her allotted
portion of it. In such situations, Oracle automatically purges
objects belonging to that user in that tablespace.
In addition, there are several ways you can manually control the
recycle bin. If you want to purge the specific table named TEST
from the recycle bin after its drop, you could issue
PURGE TABLE TEST;
or using its recycle bin name:
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";
This command will remove table TEST and all dependent objects
such as indexes, constraints, and so on from the recycle bin,
saving some space. If, however, you want to permanently drop an
index from the recycle bin, you can do so using:
purge index in_test1_01;
which will remove the index only, leaving the copy of the table
in the recycle bin.
Sometimes it might be useful to purge at a higher level. For
instance, you may want to purge all the objects in recycle bin
in a tablespace USERS. You would issue:
PURGE TABLESPACE USERS;
You may want to purge only the recycle bin for a particular user
in that tablespace. This approach could come handy in data
warehouse-type environments where users create and drop many
transient tables. You could modify the command above to limit
the purge to a specific user only:
PURGE TABLESPACE USERS USER SCOTT;
A user such as SCOTT would clear his own recycle bin with
PURGE RECYCLEBIN;
You as a DBA can purge all the objects in any tablespace using
PURGE DBA_RECYCLEBIN;
As you can see, the recycle bin can be managed in a variety of
different ways to meet your specific needs.
Table Versions and Flashback
Oftentimes the user might create and drop the same table several
times, as in:
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;
At this point, if you were to flash-back the table TEST, what
would the value of the column COL1 be? Conventional thinking
might suggest that the first version of the table is retrieved
from the recycle bin, where the value of column COL1 is 1.
Actually, the third version of the table is retrieved, not the
first. So the column COL1 will have the value 3, not 1.
At this time you can also retrieve the other versions of the
dropped table. However, the existence of a table TEST will not
let that happen. You have two choices:
Use the rename option:
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
which will reinstate the first version of the table to TEST1 and
the second versions to TEST2. The values of the column COL1 in
TEST1 and TEST2 will be 1 and 2 respectively. Or,
Use the specific recycle-bin names of the table to restore. To
do that, first identify the table's recycle bin names and then
issue:
FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP
RENAME TO TEST2;
FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP
RENAME TO TEST1;
That will restore the two versions of the dropped table.
Be Warned...
The un-drop feature brings the table back to its original name,
but not the associated objects like indexes and triggers, which
are left with the recycled names. Sources such as views and
procedures defined on the table are not recompiled and remain in
the invalid state. These old names must be retrieved manually
and then applied to the flashed-back table.
The information is kept in the view named USER_RECYCLEBIN.
Before flashing-back the table, use the following query to
retrieve the old names.
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01 INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT TRIGGER
After the table is flashed-back, the indexes and triggers on the
table RECYCLETEST will be named as shown in the OBJECT_NAME
column. From the above query, you can use the original name to
rename the objects as follows:
ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
One notable exception is the bitmap indexes. When they are
dropped, they are not placed in the recycle bin--hence they are
not retrievable. The constraint names are also not retrievable
from the view. They have to be renamed from other sources.
Other Uses of Flashback Tables
Flashback Drop Table is not limited to reversing the drop of the
table. Similar to flashback queries, you can also use it to
reinstate the table to a different point in time, replacing the
entire table with its "past" version. For example, the following
statement reinstates the table to a System Change Number (SCN)
2202666520.
FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;
This feature uses Oracle Data Pump technology to create a
different table, uses flashback to populate the table with the
versions of the data at that SCN, and then replaces the original
table with the new table. To find out how far you can flashback
the table, you could use the versioning feature of Oracle
Database 10g. (See the Week 1 installment of this series for
more details.) It is also possible to specify a timestamp
instead of SCN in the flashback clause.
|
