FlashBacks (UnDroping A Table)

It is a beautiful day outside, the sun is shining and a cool breeze is gently blowing… and then the phone rings. “Opps, I dropped a table! I dropped the big, I need it now to complete the production process table!”

This table was 611 gbs in size. The process that creates it takes many hours (36) to complete as there are many steps and joins that make it up.

So as not to keep you in suspense, I used the flashback feature of Oracle 10R2 to recover it from the “recycle bin”. It took less than 1 second to accomplish the recovery.

Note: This is Oracle 10gR2 (10.2.0.1) on SunOS 10.
This base is not running in archive log mode.

Here is the syntax from an example:

Note: Don't use this next line unless you really want to empty your recycle bin!
11:48:07 SQL> purge recyclebin;
Recyclebin purged.

11:48:21 SQL> show recyclebin
11:48:29 SQL>

11:48:49 SQL> create table test1
11:48:59 2 as
11:49:00 3 select * from dba_tables;
Table created.

11:49:07 SQL> drop table test1;
Table dropped.

11:49:23 SQL> show recyclebin
ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC
-------------------------------- ------------------------------
OBJTYPE_PLUS_SHOW_RECYC DROPTIME_PLUS_SHOW_
------------------------- -------------------
TEST1 BIN$FX3qmRLpXRfgRAADug+/iA==$0
TABLE 2006-06-05:11:49:23

11:49:31 SQL> flashback table test1 to before drop;
Flashback complete.

11:51:47 SQL> show recyclebin;

11:52:34 SQL> select count(1) from test1;
COUNT(1)
--------------------
1,970

It is as simple as that.

Now if you do not want to have a table go to the recycle bin when you drop a table… then:

11:54:10 SQL> drop table test1 purge;

Table dropped.

Elapsed: 00:00:00.82
11:54:30 SQL> show recyclebin;

To purge a user recycle bin:
11:54:32 SQL> purge recyclebin;
Whatever user you are logged in as at the time of this statement execution will have their recycle bin objects purged.

If you want to purge all users recycle bins:
11:55:01:17 SQL> purge dba_recyclebin; (for 9i and 10R1)

11:55:12:22 SQL> connect sys/ as sysdba
Connected
11:55:12:83 SQL> purge dba_recyclebin; (for 10gR2)

Thanks