First, a quick review of the basics. There are two recyclebin
views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the
synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is
enabled by default in 10g, but you can turn it on or off with the
RECYCLEBIN initialization parameter, at the system or session level.
sql>
alter session set recyclebin=off;
sql>
alter system set recyclebin=on;
When the recyclebin is enabled, any tables that you drop do not
actually get deleted. Instead, when you drop a table, Oracle just
renames the table and all its associated objects (indexes, triggers, LOB
segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> create table tst (col varchar2(10), row_chng_dt date);
Table created.
SQL> insert into tst values ('Version1', sysdate);
1 row created.
SQL> select * from tst ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
If the RECYCLEBIN initialization parameter is set to ON (the default
in 10g), then dropping this table will place it in the recyclebin:
SQL> drop table tst;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ ------------- ----- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
All that happened to the table when we dropped it was that it got
renamed. The table data is still there and can be queried just like a
normal table:
SQL> alter session set nls_date_format='HH24:MI:SS' ;
Session altered.
SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
Since the table data is still there, it's very easy to "undrop" the
table. This operation is known as a "flashback drop". The command is
FLASHBACK TABLE... TO BEFORE DROP, and it simply renames the BIN$...
table to its original name:
SQL> flashback table tst to before drop;
Flashback complete.
SQL> select * from tst ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
SQL> select * from recyclebin ;
no rows selected
It's important to know that after you've dropped a table, it has only
been renamed; the table segments are still sitting there in your
tablespace, unchanged, taking up space. This space still counts
against your user tablespace quotas, as well as filling up the
tablespace. It will not be reclaimed until you get the table out of the
recyclebin. You can remove an object from the recyclebin by restoring
it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ ------------- ------------------------- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;
Table purged.
SQL> select * from recyclebin ;
no rows selected
You have several purge options. You can also purge everything from
the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges
can purge everything from all recyclebins using DBA_RECYCLEBIN; and
finally, you can purge recyclebin objects by schema and user with PURGE
TABLESPACE USER .
Unless you purge them, Oracle will leave objects in the recyclebin
until the tablespace runs out of space, or until you hit your user quota
on the tablespace. At that point, Oracle purges the objects one at a
time, starting with the ones dropped the longest time ago, until there
is enough space for the current operation. If the tablespace data files
are AUTOEXTEND ON, Oracle will purge recyclebin objects before it
autoextends a datafile.
Just as you can wind up with several versions of a file with the same
name in the Windows recycle bin, you can wind up with several versions
of a table in the Oracle recyclebin. For example, if we create and drop
the TST table twice, we'll have two versions in the recyclebin:
SQL> create table tst (col varchar2(10), row_chng_dt date);
Table created.
SQL> insert into tst values ('Version1', sysdate);
1 row created.
SQL> drop table tst;
Table dropped.
SQL> create table tst (col varchar2(10), row_chng_dt date);
Table created.
SQL> insert into tst values ('Version2', sysdate);
1 row created.
SQL> drop table tst;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ ------------- ----- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/8rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:19:53
Query the two dropped tables to verify that they are different:
SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";
COL ROW_CHNG
---------- --------
Version1 16:10:03
SQL> select * from "BIN$HGnc55/8rRPgQPeM/qQoRw==$0" ;
COL ROW_CHNG
---------- --------
Version2 16:19:45
If we issue a FLASHBACK DROP command for TST, which version will Oracle restore?
SQL> flashback table tst to before drop;
Flashback complete.
SQL> select * from tst;
COL ROW_CHNG
---------- --------
Version2 16:19:45
Oracle always restores the most recent version of the dropped object.
To restore the earlier version of the table, instead of the later
one, we can either keep flashing back until we hit the version we want,
or we can simply refer to the correct version of the table by using its
new BIN$... name. For example, dropping TST once more gives us two
versions in the recyclebin again:
SQL> drop table tst;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ ------------- ------ --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00
To flashback to the first version, refer to the BIN$... name of the first version of TST:
SQL> flashback table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" to before drop;
Flashback complete.
SQL> select * from tst;
COL ROW_CHNG
---------- --------
Version1 16:10:03
The second version is still hanging out in the recyclebin:
SQL> select object_name, original_name, operation, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION UND PUR DROPTIME
------------------------------ -------------- --------- --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST DROP YES YES 2006-09-01:16:21:00
In a modern relational database, few tables stand alone. Most will
have indexes, constraints, and/or triggers. Dropping a table also
drops these dependent objects. When you drop a table with the
recyclebin enabled, the table and its dependent objects get renamed, but
still have the same structure as before. The triggers and indexes get
modified to point to the new BIN$ table name. (Any stored procedures
that referenced the original object, though, are invalidated.) For
example:
SQL> truncate table tst;
Table truncated.
SQL> insert into tst values ('Version3', sysdate);
1 row created.
SQL> create index ind_tst_col on tst(col);
Index created.
SQL> select * from tst;
COL ROW_CHNG
---------- --------
Version3 16:26:10
SQL> drop table tst ;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
2 from recyclebin
3 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------- ------ --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:27:36
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL INDEX NO YES 2006-09-01:16:27:36
The RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:
SQL> select object_name, original_name, type, can_undrop as "UND",
2 can_purge as "PUR", droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:27:36 233031 233031
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL INDEX NO YES 2006-09-01:16:27:36 233031 233434
The PURGE_OBJECT column is the object number of the object itself;
eg. the object number of IND_TST_COL is 233434. Note the value of the
BASE_OBJECT column for IND_TST_COL: 233031, the object number of the
associated version of the TST table.
If we FLASHBACK DROP the TST table, its index will be restored - but
Oracle will not rename it to its original name. It will retain its
BIN$.. name:
SQL> flashback table tst to before drop;
Flashback complete.
SQL> select * from tst ;
COL ROW_CHNG
---------- --------
Version3 16:26:10
SQL> select index_name from user_indexes where table_name='TST' ;
INDEX_NAME
------------------------------
BIN$HGnc55/+rRPgQPeM/qQoRw==$0
I'm not sure why Oracle bothers storing the index's original name,
since it doesn't seem to be used for anything. If we now drop this copy
of the TST table, Oracle doesn't "remember" that the original name of
the index "BIN$HGnc55/+rRPgQPeM/qQoRw==$0"was IND_TST_COL - the
ORIGINAL_NAME column in RECYCLEBIN holds the ugly string
"BIN$HGnc55/+rRPgQPeM/qQoRw==$0" :
SQL> drop table tst;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:31:43 233031 233031
BIN$HGnc56AArRPgQPeM/qQoRw==$1 BIN$HGnc55/+rRP INDEX NO YES 2006-09-01:16:31:43 233031 233434
gQPeM/qQoRw==$0
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index
(displayed as "UND" and "PUR" above). An index cannot be undropped
without the table - so CAN_UNDROP is set to NO. It can, however, be
purged without purging the table:
SQL> purge index "BIN$HGnc56AArRPgQPeM/qQoRw==$1" ;
Index purged.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:31:43 233031 233031
Now, if we restore the table, it will be restored without the index:
SQL> flashback table tst to before drop;
Flashback complete.
SQL> select * from tst ;
COL ROW_CHNG
---------- --------
Version3 16:26:10
SQL> select index_name from user_indexes where table_name='TST' ;
no rows selected
If you drop a table with associated LOB segments, they are handled in
a similar way, except that they cannot be independently purged:
CAN_UNDROP and CAN_PURGE are set to NO, and they are purged if you purge
the table from the recyclebin, restored with the table if you restore
it.
A few types of dependent objects are not handled like the simple index above.
- Bitmap join indexes are not put in the recyclebin when their base
table is DROPped, and not retrieved when the table is restored with
FLASHBACK DROP.
- The same goes for materialized view logs; when you drop a table, all
mview logs defined on that table are permanently dropped, not put in
the recyclebin.
- Referential integrity constraints that reference another table are
lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the
recyclebin, it purges indexes first. If you FLASHBACK DROP a table
whose associated indexes have already been purged, it will be restored
without the indexes.
In Windows, you can choose to permanently delete a file instead of
sending it to the recycle bin. Similarly, you can choose to drop a
table permanently, bypassing the Oracle recyclebin, by using the PURGE
clause in your DROP TABLE statement.
SQL> purge recyclebin;
Recyclebin purged.
SQL> select * from recyclebin;
no rows selected
SQL> create table my_new_table (dummy varchar2(1));
Table created.
SQL> drop table my_new_table purge;
Table dropped.
SQL> select * from recyclebin;
no rows selected
If you disable the recyclebin at the session level, with ALTER
SESSION SET RECYCLEBIN=OFF, it has the same effect as putting PURGE at
the end of all your drop statements. Note, however, that you can still
use FLASHBACK DROP to restore objects that were put in the recyclebin
before you set RECYCLEBIN=OFF. For example:
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ ------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:34:12 233031 233031
SQL> alter session set recyclebin=off ;
Session altered.
SQL> create table tst (col varchar2(10), row_chng_dt date);
Table created.
SQL> insert into tst values ('Version5', sysdate);
1 row created.
SQL> drop table tst ;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:34:12 233031 233031
SQL> flashback table tst to before drop;
Flashback complete.
SQL> select * from tst ;
COL ROW_CHNG
---------- --------
Version3 16:26:10
- Oracle drops most dependent objects along with the table, and restores
them when the table is restored with FLASHBACK DROP, but does not
restore their names. You can purge dependent objects separately to
restore the table without them.
- Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.
