FLASHBACK TABLE table_name TO BEFORE DROP;
There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.
For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN.
Example:
create table nahar_test1 (col varchar2(10), row_chng_dt date);
insert into nahar_test1 values ('Version1', sysdate);
commit;
Drop table nahar_test1;
select object_name, original_name, type
, can_undrop as "UND"
, can_purge as "PUR", droptime
from USER_RECYCLEBIN where original_name='NAHAR_TEST1';
alter session set nls_date_format='HH24:MI:SS';
select * from "BIN$j35MMYy7Q1LgRAAVFyazQA==$0";FLASHBACK TABLE nahar_test1 TO BEFORE DROP; -------------------wow!! The table Backed!!!
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
OBJECT_NAME ----- -----------------------------ORIGINAL_NAME ----- TYPE ---UND - PUR ----- DROPTIME
---------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0----- TST -------------------TABLE ----YES -- YES ---006-09-01:16:10:12
PURGE TABLE "BIN$HGnc55/7rRPgQPeM/qQoRw==$0"; --------------Purged/Permanently deleted!
CONCLUSION:
Scenario: One of our developer accidentally dropped a table from local database. And we don't keep any rman backup, or regular dump for our local database. Then I had to recover the table from recyclebin.
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. 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$.
Example:
create table nahar_test1 (col varchar2(10), row_chng_dt date);
insert into nahar_test1 values ('Version1', sysdate);
commit;
Drop table nahar_test1;
select object_name, original_name, type
, can_undrop as "UND"
, can_purge as "PUR", droptime
from USER_RECYCLEBIN where original_name='NAHAR_TEST1';
alter session set nls_date_format='HH24:MI:SS';
select * from "BIN$j35MMYy7Q1LgRAAVFyazQA==$0";FLASHBACK TABLE nahar_test1 TO BEFORE DROP; -------------------wow!! The table Backed!!!
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 (by flashback), or by purging (permanent delete) it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
OBJECT_NAME ----- -----------------------------ORIGINAL_NAME ----- TYPE ---UND - PUR ----- DROPTIME
---------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0----- TST -------------------TABLE ----YES -- YES ---006-09-01:16:10:12
PURGE TABLE "BIN$HGnc55/7rRPgQPeM/qQoRw==$0"; --------------Purged/Permanently deleted!
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
CONCLUSION:
The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly. 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.
