Recyclebin, one of the new features that comes with Oracle 10g, enables us to restore accidently dropped table. But there is one caveat with recyclebin -- the dropped table cannot be in SYSTEM tablespace. That is, if a table that is stored in SYSTEM tablespace is dropped, you wont find it in recyclebin and cannot be restored from it.

Here is an example:

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Oct 26 18:28:45 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show recyclebin
SQL> create table t1 tablespace system as select * from user_objects;

Table created.

SQL> drop table t1;

Table dropped.

SQL> select count(*) from user_objects;

COUNT(*)
----------
22933

SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show recyclebin


SQL> create table t2 tablespace users as select * from user_objects;

Table created.

SQL> drop table t2;

Table dropped.

SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

T2 BIN$Ad5TLYmzSgupTvenuIMQig==$0 TABLE 2008-10-26:19:05:50

Now, you can see that if the dropped table is not in the SYSTEM　tablespace, it can be found in the recyclebin. ;-)