The Recycle Bin does not Store 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>

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>

ISDEFAULT

There is a column in V$PARAMETER called ISDEFAULT:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         TRUE

SQL>

You might think that this tells you whether a particular parameter is set to its default value but this is not the case. If you add a recyclebin=on line to the parameter file and bounce the database, the same query runs as follows:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         FALSE

SQL>

That’s because an ISDEFAULT value of FALSE simply means that the parameter has been included in the parameter file. You can see what I mean on the screen print below (click to enlarge it). On the left is a SQL*Plus session which lists those parameters with ISDEFAULT = FALSE and on the right is the database’s parameter file. You will see that they match: