What Is the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin.
It can be recovered at a later time. This feature is called Flashback Drop.

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space.

Enabling and Disabling the Recycle Bin

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

For example:

1-

create user vahid identified by vahid account unlock;

grant connect , resource to vahid;

--------------------------------------------------------------------

2-

create table vahid.TBL_UNCOMIITED_TRN2(id number,name varchar2(30));

insert into vahid.TBL_UNCOMIITED_TRN2 values(1,'test1');

commit;

create index IDX_ID_PK on vahid.TBL_UNCOMIITED_TRN2(ID);

--------------------------------------------------------------------

3-

select * from vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

4-

drop table vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

5-

select object_name,original_name,type,can_undrop as "undo",can_purge as "PUR",droptime 

       from dba_recyclebin where original_name like 'TBL_UNCOMIITED_TRN2%' order by droptime;

  OBJECT_NAME                     ORIGINAL_NAME    TYPE undo  PUR   DROPTIME

BIN$JZgAyoU+u1DgUxQVCApelQ==$0 TBL_UNCOMIITED_TRN2  TABLE YES YES   2015-11-28:13:39:27

select * from vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0";

--------------------------------------------------------------------

6-

flashback table vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0" to before drop;

--------------------------------------------------------------------

7-

select * from vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

8-

--Restoring Dependent objects

select object_name,original_name,type from recyclebin;

select index_name from dba_indexes where table_name='TBL_UNCOMIITED_TRN2';

    INDEX_NAME

          -----------------

 BIN$JZgAyoU9u1DgUxQVCApelQ==$0

--Restore the original name of the index as follow: 

alter index "BIN$JZgAyoU9u1DgUxQVCApelQ==$0" rename to IDX_ID_PK;

--------------------------------------------------------------------

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space.

PURGE TABLE JZgAyoU9u1DgUxQVCApelQ==$0;

Users can purge the recycle bin of their own objects, and release space for objects

PURGE RECYCLEBIN;