Tuesday, May 21, 2013

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.

No comments: