DROP TABLE ... PURGE

I tested this on Oracle 11.2.0.2.7. If the recyclebin is in use:

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'recyclebin'

  3  /

 

VALUE

----------

on

 

SQL> 

... and you create a table in a tablespace other than system: 

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Table created.

 

SQL>

... it goes in the recyclebin when you drop it:

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

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

TAB1

 

SQL>

… and you can restore it from there if you need to: 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback complete.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                NUMBER

 

SQL>

If you do not want this to happen, add the word purge at the end of the drop table statement:

SQL> drop table tab1 purge

  2  /

 

Table dropped.

 

SQL>

... then it does not go into the recyclebin when you drop it: 

SQL> select original_name from recyclebin

  2  /

 

no rows selected

 

SQL>

... and you cannot restore it afterwards:

SQL> flashback table tab1 to before drop

  2  /

flashback table tab1 to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

 

SQL>