Saturday, May 10, 2014

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
 

No comments: