What is Oracle RECYCLEBIN ?

RECYCLEBIN is one of the new and core feature of version 10G.

This is similar to recyclebin on Mircosoft Windows. In MS Windows temporarily deleted files are kept in recyclebin and you can selectively restore the files you want. Similar to this option Oracle came up with RECYCLEBIN where in dropped tables are kept in RECYCLEBIN and you can selectively restore the tables.


SQL> desc recyclebin
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER




SQL> select * from recyclebin;

no rows selected

SQL> show recyclebin
SQL> create table emp211 as select * from emp;

Table created.

SQL> select count(*) from emp211;

  COUNT(*)
----------
        19

SQL> drop table emp211;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP211           BIN$TGK1xTOaRLqOzs3TRtxHvQ==$0 TABLE        2011-08-16:07:59:42




SQL> flashback table emp211 to before drop;

Flashback complete.

SQL> select count(*) from emp211;

  COUNT(*)
----------
        19


To permanently delete all tables in recyclebin, use the following statement.

SQL> purge table emp211;

Table purged.









To drop all the tables in recyclebin use the following statement.

SQL> purge recyclebin;

Recyclebin purged.