Database Reference
In-Depth Information
TABle 8.2
DBA_RECYCLEBIN
Columns
(continued)
Column Name
Description
BASE_OBJECT
Object number of the base object (in other words, the original
table that was dropped).
PURGE_OBJECT
Object number of the current Recycle Bin object. This is the object
number that will be purged.
SPACE
Number of blocks used by the object.
Tables residing in the Recycle Bin can be queried directly, just like any other table. The only
caveat is that they cannot have any Data Definition Language (DDL) or Data Manipulation
Language (DML) statements performed on them. Any attempt to do so will result in an error.
To query a table currently residing in the Recycle Bin, simply use the system-assigned
name (
OBJECT_NAME
), not the original name. Also, since this name consists of mixed-case
characters, you must enclose the name in double quotes in your
SELECT
statement. Here's
an example—please note, failure to enclose the object name in double quotes will result in
an
ORA-00933: SQL command not properly ended
error:
SQL> select * from "BIN$NIaEC81/SJ24Dqr+fTvqTQ==$0";
JOB_ID
----------
1
Purging the Recycle Bin
As was mentioned previously, objects are never physically moved into the Recycle Bin.
Therefore, there is no tablespace associated with it. Instead, the objects remain in their
respective tablespaces but are no longer listed in the data dictionary. So, they appear to
have been dropped, yet they are still available for recovery.
However, this poses a problem for a user who is dropping tables to reclaim space in a
tablespace. Or perhaps you have a temporary table that you have no further need for and
will never need to restore. For situations like these, the purge option should be used. Purging
objects from the Recycle Bin will remove them permanently and release the storage space
that they were occupying. However, it also means that they cannot be restored.
Purging can be accomplished in several ways. The first is by adding the
PURGE
option to
the command, as shown here:
SQL> drop table employees purge;
Table dropped
Search WWH ::
Custom Search