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>

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

Recyclebin does not Keep Tables from the SYSTEM Tablespace

Oracle introduced the recyclebin in version 10. This post shows that tables in the SYSTEM tablespace do not go into the recyclebin when they are dropped. It was tested on Oracle 11.2.0.2.7. First I created a table in the USERS tablespace: 

SQL> conn / as sysdba

Connected.

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Table created.

SQL>

Then I dropped the table, checked that I could see it in the recyclebin and restored it:

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

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

TAB1

 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback complete.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                NUMBER

SQL>

Then I repeated the process but created the table in the SYSTEM tablespace instead:

SQL> create table tab2

  2  (col1 number)

  3  tablespace system

  4  /

 

Table created.

 

SQL> drop table tab2

  2  /

 

Table dropped.

SQL>

This time, when I looked in the recyclebin, it was not there:

 

SQL> select original_name from recyclebin

  2  /

 

no rows selected

SQL>

... and when I tried to restore the table, I was unable to do so: 

 

SQL> flashback table tab2 to before drop

  2  /

flashback table tab2 to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

 

SQL> 

In Spanish / en español 

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.