Using the
FLASHBACK TABLE command, you can undo the effects of a DROP TABLE statement
without having to use point-in-time recovery
Note: The RECYCLEBIN initialization
parameter is used to control whether the Flashback Drop capability is turned ON
or OFF. If the parameter is set to OFF, then dropped tables do not go into the
recycle bin. If this parameter is set to ON, the dropped tables go into the
recycle bin and can be recovered. By default, RECYCLEBIN is set to ON.
Recycle
Bin
Without the
recycle bin enabled, when you drop a table, the space associated with the table
and its dependent objects is immediately reclaimable (that is, it can be used
for other objects).
If the
recycle bin is enabled, when you drop a table, then the space associated with
the table and its dependent objects is not immediately reclaimable, even though
it does appear in DBA_FREE_SPACE. Instead, the dropped objects are referenced
in the recycle bin and still belong to their owner. The space used by recycle
bin objects is never automatically reclaimed unless there is space pressure.
This enables you to recover recycle bin objects for the maximum possible
duration.
When a
dropped table is “moved” to the recycle bin, the table and its associated
objects and constraints are renamed using system-generated names. The renaming
convention is as follows:
BIN$unique_id$version
where
unique_id is a 26-character globally unique identifier for this object making
the recycle bin name unique across all databases and version is a version
number assigned by the database.
The recycle
bin itself is a data dictionary table that maintains the relationships between
the original names of dropped objects and their system-generated names. You can
query the recycle bin by using the DBA_RECYCLEBIN view.
1. You have created a table called EMPLOYEES in
your tablespace.
2. You drop
the EMPLOYEES table.
3. The
extents occupied by EMPLOYEES are now considered as free space.
4. EMPLOYEES
is renamed and the new name is recorded into the recycle bin.
Restoring
Tables from the Recycle Bin
Use the
FLASHBACK TABLE ... TO BEFORE DROP command to recover a table and all of its
possible dependent objects from the recycle bin. You can specify either the
original name of the table or the system-generated name assigned to the object
when it was dropped.
If you
specify the original name, and if the recycle bin contains more than one object
of that name, then the object that was moved to the recycle bin most recently
is recovered first (LIFO: last in, first out). If you want to retrieve an older
version of the table, you can specify the system-generated name of the table
that you want to retrieve, or issue additional FLASHBACK TABLE ... TO BEFORE
DROP statements until you retrieve the table you want.
If a new
table of the same name has been created in the same schema since the original
table was dropped, then an error is returned unless you also specify the RENAME
TO clause.
Note: When you flash back a dropped
table, the recovered indexes, triggers, and constraints keep their recycle bin
names. Therefore, it is advisable to query the recycle bin and DBA_CONSTRAINTS
before flashing back a dropped table. In this way, you can rename the recovered
indexes, triggers, and constraints to more usable names.
FLASHBACK
TABLE <table_name> TO BEFORE DROP
[RENAME TO <new_name>];
[RENAME TO <new_name>];
Recycle
Bin: Automatic Space Reclamation
As long as
the space used by recycle bin objects is not reclaimed, you can recover those
objects by using Flashback Drop. The following are the recycle bin object
reclamation policies:
•
Manual
cleanup when you explicitly issue a PURGE command
•
Automatic
cleanup under space pressure: While objects are in the recycle bin, their
corresponding space is also reported in DBA_FREE_SPACE because their space is
automatically reclaimable. The free space in a particular tablespace is then
consumed in the following order:
1. Free space not corresponding to recycle bin
objects
2. Free space corresponding to recycle bin
objects. In this case, recycle bin objects are automatically purged from the
recycle bin using a first in, first out (FIFO) algorithm.
3.
Free
space automatically allocated if the tablespace is auto-extensible Suppose you
create a new table inside the TBS1 tablespace. If there is free space allocated
to this tablespace that does not correspond to a recycle bin object, then this
free space is used as a first step. If this is not enough, free space is used
that corresponds to recycle bin objects that reside inside TBS1. If the free
space of some recycle bin objects is used, then these objects get purged
automatically from the recycle bin. At this time, you can no longer recover
those objects by using the Flashback Drop feature. As a last resort, if space
requirement is not yet satisfied, the TBS1 tablespace is extended if possible.
Recycle
Bin: Manual Space Reclamation
Use the
PURGE command to permanently remove objects from the recycle bin. When an
object is purged from the recycle bin, the object and its dependent objects are
permanently removed from the database. As a consequence, objects purged from
the recycle bin are no longer recoverable by using the Flashback Drop feature.
The following are the possible uses of PURGE:
•
PURGE
TABLE purges the specified table.
•
PURGE
INDEX purges the specified index.
•
PURGE
TABLESPACE purges all the objects residing in the specified tablespace. In
addition, objects residing in other tablespaces may get purged if they are
dependent.
•
PURGE
RECYCLEBIN purges all the objects that belong to the current user. RECYCLEBIN
and USER_RECYCLEBIN are synonymous.
•
PURGE
DBA_RECYCLEBIN purges all the objects. You must have enough system privileges
or the SYSDBA system privilege to issue this command.
Tables can
also be purged from the recycle bin using Enterprise Manager. On the Schema
folder tab, click Tables, then select the schema the dropped object resided in
and click the Recycle Bin button. Select the table from the results list and
click the Purge button.
Note: For PURGE TABLE and PURGE INDEX
commands, if you specify an original name and
if the recycle bin contains more than one object of that name, then the object that has been in the recycle bin the longest is purged first (FIFO).
if the recycle bin contains more than one object of that name, then the object that has been in the recycle bin the longest is purged first (FIFO).
Bypassing
the Recycle Bin
You can use
the DROP TABLE PURGE command to permanently drop a table and its dependent
objects from the database. When you use this command, the corresponding objects
are not moved to the recycle bin. This command provides the same functionality
that the DROP TABLE command provided in previous releases.
When you
issue the DROP TABLESPACE ... INCLUDING CONTENTS command, the objects in the
tablespace are not placed in the recycle bin. Moreover, objects in the recycle
bin belonging to the tablespace are purged. When you issue the same command
without the INCLUDING CONTENTS clause, the tablespace must be empty for the
command to succeed. However, there can be objects belonging to the tablespace
in the recycle bin. In this case, these objects are purged.
When you
issue the DROP USER ... CASCADE command, the user and all the objects owned by
the user are permanently dropped from the database. Any objects in the recycle
bin belonging to the dropped user are purged.
DROP
TABLE <table_name> [PURGE] ;
DROP
TABLESPACE <ts_name> [INCLUDING CONTENTS] ;
DROP
USER <user_name> [CASCADE] ;
Querying
the Recycle Bin
You can
view all the objects that you have dropped by querying user_recyclebin or
RECYCLEBIN. It has a synonym RECYCLEBIN, for ease of use.
The
dba_recyclebin view shows you all the objects that have been dropped by all
users and that are still in the recycle bin.
You can
also use the SQL*Plus SHOW RECYCLEBIN command. This command shows you only
those objects that can be “undropped.”
The
examples show how to extract important information from the recycle bin:
•
original_name
is the name of the object before it is dropped.
•
object_name
is the system-generated name of the object after it is dropped.
•
type
is the object’s type.
•
ts_name
is the name of the tablespace to which the object belongs.
•
droptime
is the date at which the object was dropped.
•
related
is the object identifier of the dropped object.
•
space
is the number of blocks currently used by the object.
You can
also see the content of the recycle bin by using Database Control.
Querying
Data from Dropped Tables
When you
drop a table, its original name is changed to a unique, system-generated name
referenced in the recycle bin. Because you still own the dropped table, you can
still see its characteristics from various dictionary views such as DBA_TABLES,
DBA_OBJECTS, DBA_SEGMENTS, and so on. To make a distinction between tables that
are in the recycle bin and tables that are not, the DBA_TABLES view has a
column called DROPPED that is set to YES for tables that were dropped but are
still in the recycle bin.
So, as long
as a system-generated table name is in the recycle bin, you can use it in a
SELECT statement and also in flashback queries.
However,
you cannot issue any sort of DML or DDL statements on objects that reside in
the recycle bin.
